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 and BUG #17870).
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.