Re: BUG #17885: slow planning constraint_exclusion

On Wed, Apr 5, 2023 at 8:54 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Wed, 5 Apr 2023 at 10:16, David Rowley <dgrowleyml(at)gmail(dot)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