Re: Should we add GUCs to allow partition pruning to be disabled?

On 2 May 2018 at 07:24, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Apr 24, 2018 at 5:59 PM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services