Re: Faster distinct query?

Heh, I honestly forgot about the recursive CTE. Certainly worth a try and
wouldn't require installing other extensions.

This is what depesz is referring to:
https://wiki.postgresql.org/wiki/Loose_indexscan

On Thu, Sep 23, 2021 at 3:04 AM hubert depesz lubaczewski <depesz(at)depesz(dot)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;
>
> If this is not huge, then you can make the query run much faster using
> skip scan - recursive cte.
>
> Best regards,
>
> depesz
>
>
>
>