Обсуждение: Unique index prohibits partial aggregates

---------------------------------------------------------------------------
The contents of this e-mail (including any attachments) are for the intended recipients only. If you are not an intended recipient but have received this email in error, we kindly request you to inform the sender of such error and delete this email and any attachments. If you open any attachments of this email, please understand that you do so at your own risk. We have made all reasonable efforts to keep this email and its attachments free from any bugs, viruses or the like, but cannot accept any responsibility for it.
Huisman Equipment B.V. and its affiliated companies cannot take any responsibility with regard to the accuracy or completeness of the content of this email and any attachments. Please note that this email and any attachments may contain information that is considered confidential, privileged and subject to copyright or other intellectual property rights. We kindly request (and insofar legally possible, demand) you to keep the content of this email and any attachments confidential and abide to the restrictions following from such protection.
---------------------------------------------------------------------------

On Mon, 27 Jun 2022 at 23:49, Bos, Fred <fbos@huisman-nl.com> wrote:
> "Settings: effective_cache_size = '128GB', force_parallel_mode = 'on', max_parallel_workers = '40',

You shouldn't be using force_parallel_mode. It does not do what you
think. See the documentation for that GUC, or read [1]

> I expected the query to become faster
> with a unique index or column, so why does the query planner decide on group
> aggregation instead of partial aggregation?

It just simply does not know how many groups are likely to exists on
your expression.  Statistics are only gathered on bare columns. The
planner has no idea how many groups are likely to exist for
"t/(1000*3600*24)".

In PostgreSQL 14 and above you could create extended statistics for
the expression using:

create statistics t1_t_stats (ndistinct) on (t/(1000*3600*24)) from
bhload_nohyp_noin; -- pg14
analyze bhload_nohyp_noin;

for your version (pg13), you'd need to create an expression index.

create index on bhload_nohyp_noin using brin ((t/(1000*3600*24)));
analyze bhload_nohyp_noin;

I added "using brin" as a brin index is going to be a bit more
lightweight than a btree index.  You only need the index to instruct
ANALYZE to gather statistics. You might also want to ramp up the pages
per range.

With that, the planner might then realise that parallel aggregate
might be worthwhile.

David

[1] https://www.enterprisedb.com/postgres-tutorials/using-forceparallelmode-correctly-postgresql

Thank you for your reply.

I turned force_parallel_mode off.

Adding a BRIN index on this expression does indeed improve the performance for
this particular expression (it triggers the faster partial hash aggregate
again). However, my expression is variable, so "t/(1000*3600*24)" actually
is "t/dt", where dt changes frequently. When dt is changed, the query planner
reverts to GroupAggregate again (because the BRIN index is not suitable).

Also, when postgres doesn't know anything about the table, just after
importing, it has shown that it is capable of doing the partial hash
aggregate operation on the same table very quickly.

To elaborate on this:

If I setup the table with this query,

        CREATE TABLE IF NOT EXISTS table (
                t BIGINT NOT NULL,
                v REAL NULL
        );

then insert the rows,

        INSERT INTO table .....;
                about 30million rows,

and then directly execute the query,

        SELECT t/dt as time, avg(v)
        FROM table
        GROUP BY time
        ORDER BY time;

the query executes in under 2 seconds for any dt.

However, the query runs in about 20 seconds when I do the any of following:

   - VACCUM ANALYZE and, then running the same query,
   - or, CREATE UNIQUE INDEX ON table USING btree (t), and then running the
     same query.

So it is possible to run the query much faster but postgres won't do it.

Is this because when the statistics are unknown, it expects a relatively low
amount of groups and opts for a partial plan?

Finally, is there a way to force postgres to do the partial hash aggregate,
either by changing a setting or by influencing the expected amount of output
groups for each query?

Regards,
Fred

---------------------------------------------------------------------------
The contents of this e-mail (including any attachments) are for the intended recipients only. If you are not an
intendedrecipient but have received this email in error, we kindly request you to inform the sender of such error and
deletethis email and any attachments. If you open any attachments of this email, please understand that you do so at
yourown risk. We have made all reasonable efforts to keep this email and its attachments free from any bugs, viruses or
thelike, but cannot accept any responsibility for it. 
Huisman Equipment B.V. and its affiliated companies cannot take any responsibility with regard to the accuracy or
completenessof the content of this email and any attachments. Please note that this email and any attachments may
containinformation that is considered confidential, privileged and subject to copyright or other intellectual property
rights.We kindly request (and insofar legally possible, demand) you to keep the content of this email and any
attachmentsconfidential and abide to the restrictions following from such protection. 
---------------------------------------------------------------------------

On Wed, 29 Jun 2022 at 00:45, Bos, Fred <fbos@huisman-nl.com> wrote:
> Finally, is there a way to force postgres to do the partial hash aggregate,
> either by changing a setting or by influencing the expected amount of output
> groups for each query?

You could do something like:

ALTER TABLE bhload_nohyp_noin ALTER COLUMN t SET (n_distinct = 200);
ANALYZE bhload_nohyp_noin;

Please be aware that this may have detrimental effects if you do any
joins or group bys directly on this column.  Otherwise, providing you
don't have a unique index on that column, then it should trick the
planner into thinking there will be fewer groups than it currently
thinks there will be, which will likely result in the parallel plan
that you desire.

David