Re: Problem with default partition pruning

On 2019-Aug-07, Simon Riggs wrote:

> 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.

Well, as the commit message noted, it applies to other cases also, not
just the default partition. The default partition just happens to be
the most visible case.

> "So in certain cases
> we're scanning partitions that we don't need to."
>
> Avoiding that has been the subject of months of work.

Well, yes, avoiding that is the point of this commit also: we were
scanning some partitions for some queries, after this patch we're
supposed not to.

> "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.

I'll have a look.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services