> On Sep 22, 2021, at 11:04 PM, hubert depesz lubaczewski <dep...@depesz.com> > wrote: > > On Wed, Sep 22, 2021 at 12:05:22PM -0800, Israel Brewster wrote: >> 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) > > It looks that there is ~ 170 stations, and ~ 800 million rows int he > table. > > can you tell us how many rows has this: > > select distinct station, channel from data;
At the moment, about 170, but I would expect it to stabilize at around 510 or less once I am pulling in all the channels. Getting this query (or the stored/cached results thereof, as it shouldn’t change too often) working fast enough to be used in the live system is simply the first step to pulling in three times as much data (that’ll be fun!) > > If this is not huge, then you can make the query run much faster using > skip scan - recursive cte. Sounds like something to look into. Of course, if I go with a lookup table, updated by an on insert trigger, it becomes a moot point. I’ll have to spend some time wrapping my head around the concept, and figuring out how to write it so that I get distinct per station rather than just a straight up distinct, but theoretically at least it makes sense. Thanks! --- 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 > > Best regards, > > depesz >
