Re: Should we add GUCs to allow partition pruning to be disabled? - Mailing list pgsql-hackers
| From | David Rowley |
|---|---|
| Subject | Re: Should we add GUCs to allow partition pruning to be disabled? |
| Date | |
| Msg-id | CAKJS1f8Y1aPGnOc_7jmagUiNi8yM4W_ZrXodNV6ziJ43xqVEJg@mail.gmail.com Whole thread Raw |
| In response to | Re: Should we add GUCs to allow partition pruning to be disabled? (Robert Haas <robertmhaas@gmail.com>) |
| Responses |
Re: Should we add GUCs to allow partition pruning to be disabled?
(David Rowley <david.rowley@2ndquadrant.com>)
|
| List | pgsql-hackers |
On 2 May 2018 at 07:24, Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Apr 24, 2018 at 5:59 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> Constraint
> exclusion was pretty easy to get wrong, hence the need for a separate
> section, and I suppose the new partition pruning may be prey to the same
> problems, so it seems worth to document them specially. But not sure
> about the others, if they are mostly debugging tools.
Weighing in here late, but I have a hard time understanding why we
want a GUC to control partition pruning at all. With constraint
exclusion, the issue is whether you want to spend planner cycles to
try to deduce things using CHECK constraints when, quite possibly,
your CHECK constraints are unrelated to table inheritance and thus
won't help. But seems extremely unlikely that the same thing would
happen with partition pruning. Unlike your CHECK constraints, your
partition bounds are, by definition, potentially useful for pruning.
Thanks for weighing in here. It's certainly true that I was a bit undecided about this, hence the subject. I ended up leaning more towards having the GUC due to the fact that partition pruning, although much cheaper than constraint exclusion, it's still not free. There's a good chance of there being workloads that just never benefit from it. People running those workloads might be quite glad we added the ability to switch it off.
It might be worth running a series of benchmarks to test where the worst case performance hit is with partition pruning. We'd need some fast to execute query that has items in the WHERE clause, but none that match the partition key. It should be easy to test the overhead of this now that the GUC is committed. Perhaps if we're unable to measure the performance drop then the GUC is not worth it, but if we can, then perhaps it is, as it will help speed up someone's workload. I'll try to do this today. I imagine something like: SELECT * FROM parttable WHERE non_part_key_but_indexed_col IN(<long list of values that matches almost 0 rows>) might be the best bet.
Another reason to have the GUC is in case some bug is discovered in the pruning code. Being able to disable it could be useful until we can release a minor version containing a fix. From my time reviewing the faster partition pruning code, I very much am aware that it's not simple code, so it would not surprise me if we find a few bugs in it down the track. The problem with this reason is that it carries less weight every day that passes with no bug discovered. If no bug is found in 10 years then we'll likely wonder why we bothered doing it for this reason. Lack of any sort of crystal ball makes it hard to know what to do here, so let's focus on the performance reason first.
pgsql-hackers by date: