Re: Problem with default partition pruning

On Tue, 6 Aug 2019 at 23:18, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> On 2019-Aug-06, Alvaro Herrera wrote:
>
> > Well, if this is really all that duplicative, one thing we could do is
> > run this check in get_partprune_steps_internal only if
> > constraint_exclusion is a value other than on; we should achieve the
> > same effect with no repetition. Patch for that is attached. However,
> > if I run the server with constraint_exclusion=on, the regression test
> > fail with the attached diff. I didn't look at what test is failing, but
> > it seems to me that it's not really duplicative in all cases, only some.
> > Therefore we can't do it.
>
> Right ... One of the failing cases is one that was benefitting from
> constraint_exclusion in the location where we were doing it previously.
>
> I think trying to fix this problem by selectively moving where to apply
> constraint exclusion would be very bug-prone, and hard to detect whether
> we're missing one spot or doing it multiple times in some other cases.
> So I think we shouldn't try. If this is a real problem, then we should
> add a flag to the reloptinfo and set it when we've done pruning, then
> do nothing if we already did it. I'm not sure that this is correct, and
> I'm even less sure that it is worth the trouble.
>
> In short, I propose to get this done as the patch I posted in
> https://postgr.es/m/20190806133053.GA23706@alvherre.pgsql

I saw your recent commit and it scares me in various places, noted below.

"Commit: Apply constraint exclusion more generally in partitioning"

"This applies particularly to the default partition..."

My understanding of the thread was the complaint was about removing the
default partition. I would prefer to see code executed just for that case,
so that people who do not define a default partition are unaffected.

"So in certain cases
we're scanning partitions that we don't need to."

Avoiding that has been the subject of months of work.

"This has the unwanted side-effect of testing some (most? all?)
constraints more than once if constraint_exclusion=on. That seems
unavoidable as far as I can tell without some additional work, but
that's not the recommended setting for that parameter anyway.
However, because this imposes additional processing cost for all
queries using partitioned tables..."

One of the major features of PG12 is the additional performance and
scalability of partitoning, but we don't seem to have benchmarked the
effect of this patch on those issues.

Please could we do perf checks, with tests up to 1000s of partitions? And
if there is a regression, I would vote to revoke this patch or address the
request in a less general way.

Hopefully I have misunderstood and/or there is no regression.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Solutions for the Enterprise