From: | Israel Brewster <ijbrewster(at)alaska(dot)edu> |
---|---|
To: | Rob Sargent <robjsargent(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Growth planning |
Date: | 2021-10-04 22:04:11 |
Message-ID: | 296A9CF2-0E66-4925-A806-FCFE2E14287A@alaska.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On Oct 4, 2021, at 9:22 AM, Rob Sargent <robjsargent(at)gmail(dot)com> wrote:
>
> I think the date-station-channel could "take over" for the station-date. Naturally the latter is chosen if you give just the two fields, but I would be curious to see how well the former performs given just its first two fields(when station-date doesn't exist).
>
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 <https://explain.depesz.com/s/COfy> vs the one for the date-station-channel index here: https://explain.depesz.com/s/hgBt <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
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2021-10-04 22:05:34 | Re: Growth planning |
Previous Message | Israel Brewster | 2021-10-04 21:37:49 | Re: Growth planning |