On Thu, 23 Sept 2021 at 08:27, David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Wed, Sep 22, 2021 at 1:21 PM Michael Lewis <mlewis@entrata.com> wrote:
>> If there is correlation between station & channel, then you might look at creating a multivariate statistics object
andanalyzing 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
likean optimal way to obtain this data and the existing query already does that. The aggregation path might vary
thoughit 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;
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
