Interesting result here. Technically it appears you are correct - the date-station-channel index *can* “take over” for the station-date index. Unfortunately, it is about 6x slower (see the EXPLAIN ANALYZE output for the station_date_idx here: https://explain.depesz.com/s/COfy vs the one for the date-station-channel index here: https://explain.depesz.com/s/hgBt) - using the station_date_idx takes around 2.5 seconds while the date-station-channel index is over 12 seconds, even though it has an apparently simpler execution plan. Perhaps something about the different sizes of the indexes? The query I used in both cases was this: SELECT to_char(datetime AT TIME ZONE 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"') as text_date, freq_max10, sd_freq_max10, rsam FROM data WHERE datetime>='2021-09-27' AND station=27 AND channel=‘BHZ' Which actually includes all three columns (which makes it even more interesting to me that the two column, non-UNIQUE index is preferable), and I ran the query several times both with and without the station-date index to (hopefully) make sure there were no caching issues. --- Israel Brewster Software Engineer Alaska Volcano Observatory Geophysical Institute - UAF 2156 Koyukuk Drive Fairbanks AK 99775-7320 Work: 907-474-5172 cell: 907-328-9145 |
