Re: Faster distinct query?

[Israel Brewster <ijbrewster@xxxxxxxxxx>]

On Sep 22, 2021, at 5:10 PM, David Rowley <dgrowleyml@xxxxxxxxx> wrote:

On Thu, 23 Sept 2021 at 08:27, David G. Johnston
<david.g.johnston@xxxxxxxxx> wrote:

On Wed, Sep 22, 2021 at 1:21 PM Michael Lewis <mlewis@xxxxxxxxxxx> wrote:

If there is correlation between station & channel, then you might look at creating a multivariate statistics object and analyzing the table so the planner can make better choices

There is no where clause so I'm doubtful there is much to be gained going down this path.  The Index-Only scan seems like an optimal way to obtain this data and the existing query already does that.  The aggregation path might vary though it seems like that shouldn't be the case here.

ndistinct extended statistics would be used to estimate the number of
groups in the GROUP BY clause for the version of the query that
performs GROUP BY station, channel.   We've not seen the EXPLAIN
ANALYZE for that query, so don't know if there's any use for extended
statistics there.  However, if the planner was to think there were
more groups than there actually are, then it would be less inclined to
do parallelise the GROUP BY.  I think writing the query in such a way
that allows it to be parallelised is likely going to result in some
quite good performance improvements. i.e:

select station, array_agg(channel) as channels
FROM(
  SELECT station,channel FROM data GROUP BY station,channel
) AS sub
group by station;

Ah, yes indeed. That version runs in about 30 seconds rather than 5 minutes! See the explain analyze output here: https://explain.depesz.com/s/L5Bf It looks more complicated, but being able to run parallel definitely makes a difference, and there may be some other improvements in there that I’m not aware of as well!

Still not quite fast enough for real-time queries, but certainly fast enough to keep a materialized view updated.

And this is why I love postgresql and this community - when something isn’t working as well as I would like, there is usually a way to improve it drastically :-)

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

which is just the same as Michael's version but without DISTINCT.
Also, Tom's point about lots of heap fetches is going to count for
quite a bit too, especially so if I/O plays a large part in the total
query time.

David