I was wondering if there was any way to improve the performance of this query:
SELECT station,array_agg(distinct(channel)) as channels FROM data GROUP BY station; The explain execution plan can be found here: https://explain.depesz.com/s/mtxB#html <https://explain.depesz.com/s/mtxB#html> and it looks pretty straight forward. It does an index_only scan, followed by an aggregate, to produce a result that is a list of stations along with a list of channels associated with each (there can be anywhere from 1 to 3 channels associated with each station). This query takes around 5 minutes to run. To work around the issue, I created a materialized view that I can update periodically, and of course I can query said view in no time flat. However, I’m concerned that as the dataset grows, the time it takes to refresh the view will also grow (correct me if I am wrong there). This is running PostgreSQL 13, and the index referenced is a two-column index on data(station, channel) --- 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
