Horton
Menu

Thread

  1. BUG #17885: slow planning constraint_exclusion

    PG Bug reporting form <noreply@postgresql.org> — 2023-04-04T16:38:27Z

    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

  2. Re: BUG #17885: slow planning constraint_exclusion

    David Rowley <dgrowleyml@gmail.com> — 2023-04-04T22:16:28Z

    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

  3. Re: BUG #17885: slow planning constraint_exclusion

    David Rowley <dgrowleyml@gmail.com> — 2023-04-04T22:53:58Z

    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

  4. Re: BUG #17885: slow planning constraint_exclusion

    Maxim Boguk <maxim.boguk@gmail.com> — 2023-04-05T00:30:17Z

    On Wed, Apr 5, 2023 at 8:54 AM David Rowley <dgrowleyml@gmail.com> wrote:

> 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
>

Hi David,

As the person responsible for keeping the system where this problem was
observed in production working I cannot just turn off
enable_partition_pruning on a 6TB archive database with multiple huge
partitioned tables (it will have a very negative effect on the whole system
performance).
What makes the situation even worse - this slow planning time happens
during FDW access  (e.g. possible to have multiple EXPLAIN runs per actual
query see BUG #17871
<https://www.postgresql.org/message-id/17871-16521a70c16cb83c%40postgresql.org>
and BUG #17870
<https://www.postgresql.org/message-id/17870-2949e79d1b0a32e5%40postgresql.org>
).
Actual NOT IN list unfortunately could be quite long (hundred entries) and
with production planning time over 1s.
Probably a good idea to put an upper limit to the maximum amount of effort
spent on checking the base quals doesn't refute each other because in some
cases it requires a lot of cpu cycles.

-- 
Maxim Boguk
Senior Postgresql DBA
https://dataegret.com/

Phone UA: +380 99 143 0000
Phone AU: +61  45 218 5678

  5. Re: BUG #17885: slow planning constraint_exclusion

    David Rowley <dgrowleyml@gmail.com> — 2023-04-05T00:45:06Z

    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 turn off enable_partition_pruning on a 6TB archive database with multiple huge partitioned tables (it will have a very negative effect 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