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

On 2018/05/17 23:24, Robert Haas wrote:
> On Thu, May 17, 2018 at 12:04 AM, David Rowley wrote:
>> I'm not really a fan of overloading properties with a bunch of text.
>> Multiple int or text properties would be easier to deal with,
>> especially so when you consider the other explain formats. Remember,
>> all 3 pruning methods could be used for a single Append node.
>
> I was imagining it as two properties in non-text format that got
> displayed in a special way in text mode. I intended that this would
> only give information about execution-time pruning, so there would
> only two methods to consider here, but, yeah, you might have something
> like:
>
> Execution-Time Pruning: order_lines (at executor startup, at runtime)

This looks short enough and useful.

>> I guess doing work here would require additional code in the planner
>> to track how many relations were removed by both partition pruning and
>> constraint exclusion. Dunno if that would be tracked together or
>> separately. However, I'd prefer to have a clear idea of what exactly
>> the design should be before I go write some code that perhaps nobody
>> will like.
>
> I don't feel strongly about adding more code to track the number of
> removed partitions. I think that the important thing is whether or
> not partitioning is happening and at what stage, and I think it's
> useful to show the relation name if we can. As you pointed out, it's
> largely possible already to figure out how well we did at removing
> stuff and at which stages, but to me it seems quite easy to be
> confused about which stages tried to remove things. For example,
> consider:
>
> Gather
> -> Nested Loop
> -> Seq Scan
> Filter: something
> -> Append
> -> Index Scan
> -> Index Scan
> -> Index Scan
>
> I think it's going to be quite tricky to figure out whether that
> Append node is trying to do execution-time pruning without some
> annotation. The nloops values are going to be affected by how many
> rows are in which partitions and how many workers got which rows as
> well as by whether execution-time pruning worked and how effectively.
> You might be able to figure out it out by staring at the EXPLAIN
> output for a while... but it sure seems like it would be a lot nicer
> to have an explicit indicator... especially if you're some random user
> rather than a PostgreSQL expect.

Yeah, I think it'd help to have Append be annotated as suggested by Robert
above. I guess if "at executor startup" is shown, then the subnodes
listed under Append will consist of only those that survived
executor-startup pruning and thus will help understand why there are fewer
than shown with EXPLAIN (without ANALYZE). Also, if "at runtime" is
shown, a user may want look at nloops property of the individual subnodes
to guess at how much pruning has occurred; although only the latter (that
is, inspecting nloops) suffices to know that runtime pruning has occurred
as also currently written in the documentation about pruning [1], the
first piece of information (the "at runtime" annotation) seems nice to have.

Thanks,
Amit

[1]
https://www.postgresql.org/docs/devel/static/ddl-partitioning.html#DDL-PARTITION-PRUNING