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

On Thu, May 17, 2018 at 12:04 AM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>> Append
>> Execution-Time Pruning: order_lines (at executor startup)
>> -> Index Scan ...
>
> Perhaps Append should be shown as "Unordered Partitioned Table Scan on
> <table>". That seems more aligned to how else we show which relation a
> node belongs to. The partition being scanned is simple to obtain. It's
> just the first item in the partitioned_rels List. (MergeAppend would
> be an "Ordered Partitioned Table Scan")

Hmm, that's a radical proposal but I'm not sure I like it. For one
thing, table scan might mean sequential scan to some users. For
another, it's not really unordered. Unless it's parallel-aware, we're
going to scan them strictly in the order they're given.

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

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

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company