Обсуждение: BUG #17885: slow planning constraint_exclusion

The following bug has been logged on the website:

Bug reference:      17885
Logged by:          Sergei Kornilov
Email address:      sk@zsrv.org
PostgreSQL version: 15.2
Operating system:   linux
Description:

Hello

Today I was looking for the problem of one slow query and minimized the
example to such case:

create table part_test (range bigint, col_a bigint, col_b bigint) partition
by range (range);
select format($$create table part_test_%s partition of part_test for values
from ( %L ) to ( %L )$$, lpad(i::text, 3, '0'), (i-1)*1e6, i*1e6) from
generate_series(1,49) as i;
\gexec

explain (analyze,buffers) select * from part_test where col_a = 123
and col_b not in (
 0, 1, 2, 3, 4, 5, 6, 7, 8, 9,10,11,12,13,14,15,16,17,18,19,
20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,
40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,
60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,
80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99
);

(50 partitions and 100 elements "in")

With this query I am getting abnormally high planning time:

 Planning Time: 239.610 ms
 Execution Time: 0.324 ms
 
Increasing the number of partitions or size of "not in" list further
increases planning time. Reproduced on today's HEAD 16dev too. I found time
is wasted somewhere in relation_excluded_by_constraints. If I disable
constraint_exclusion completely, then the planning time drops to a few
milliseconds.

regards, Sergei

On Wed, 5 Apr 2023 at 05:14, PG Bug reporting form
<noreply@postgresql.org> wrote:
> Increasing the number of partitions or size of "not in" list further
> increases planning time. Reproduced on today's HEAD 16dev too. I found time
> is wasted somewhere in relation_excluded_by_constraints.

How did you come to the conclusion that the time is wasted?

> If I disable
> constraint_exclusion completely, then the planning time drops to a few
> milliseconds.

We still run relation_excluded_by_constraints() after partition
pruning only the remaining partitions.  I believe there were some
cases that we still didn't prune that relation_excluded_by_constraints
was able to eliminate. I don' recall the exact details of what those
cases are. I believe the call to relation_excluded_by_constraints()
was kept due to this.

You may want to just switch it off if it's too slow for you.  I don't
think anything you've shown here is worthy of being classed as a bug.

David

On Wed, 5 Apr 2023 at 10:16, David Rowley <dgrowleyml@gmail.com> wrote:
> We still run relation_excluded_by_constraints() after partition
> pruning only the remaining partitions.  I believe there were some
> cases that we still didn't prune that relation_excluded_by_constraints
> was able to eliminate. I don' recall the exact details of what those
> cases are. I believe the call to relation_excluded_by_constraints()
> was kept due to this.

I may have misremembered that.  On digging further, it seems we don't
run relation_excluded_by_constraints() using the partition constraint.
That's fairly evident by looking at the code and also noticing that we
don't prune partitions with partition_pruning=off.

The extra time is being spent checking the base quals don't refute
each other.  That's able to determine that something like the
following can't return anything:

postgres=# explain select * from part_test where col_a = col_b and
col_a <> col_b;
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: false
(2 rows)

Same recommendation as before - if you don't want it, just turn it off.

David

On Wed, 5 Apr 2023 at 12:30, Maxim Boguk <maxim.boguk@gmail.com> wrote:
> As the person responsible for keeping the system where this problem was observed in production working I cannot just
turnoff enable_partition_pruning on a 6TB archive database with multiple huge partitioned tables (it will have a very
negativeeffect on the whole system performance). 

I guess I didn't spell out what I was suggesting because you'd already
suggested it yourself, so I thought it was clear...  I should have
been more explicit.

What I was suggesting is that if you don't like the fact that
relation_excluded_by_constraints() is running for each partition in
this query, then switch it off.  As you've already mentioned, the
constraint_exclusion GUC is how that's done. The setting you'll want
to consider is "off".  You'll need to do the analysis into if there
are any places in the application that are benefiting from your
current setting of constraint_exclusion, if there are none, then you
might want to consider just changing the setting in postgresql.conf.

This not at all the same as turning off enable_partition_pruning.

David