Re: Runtime pruning problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Cc: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, Yuzuko Hosoya <hosoya(dot)yuzuko(at)lab(dot)ntt(dot)co(dot)jp>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Runtime pruning problem
Date: 2019-12-04 15:30:01
Message-ID: 19442.1575473401@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I wrote:
>> This may be arguing for a change in ruleutils' existing behavior,
>> not sure. But when dealing with traditional-style inheritance,
>> I've always thought that Vars above the Append were referring to
>> the parent rel in its capacity as the parent, not in its capacity
>> as the first child. With new-style partitioning drawing a clear
>> distinction between the parent and all its children, it's easier
>> to understand the difference.

> OK, so experimenting, I see that it is a change: HEAD does

> regression=# explain verbose select * from part order by a;
> QUERY PLAN
> ---------------------------------------------------------------------------------
> Sort (cost=362.21..373.51 rows=4520 width=8)
> Output: part_p1.a, part_p1.b
> Sort Key: part_p1.a
> -> Append (cost=0.00..87.80 rows=4520 width=8)
> -> Seq Scan on public.part_p1 (cost=0.00..32.60 rows=2260 width=8)
> Output: part_p1.a, part_p1.b
> -> Seq Scan on public.part_p2_p1 (cost=0.00..32.60 rows=2260 width=8)
> Output: part_p2_p1.a, part_p2_p1.b
> (8 rows)

> The portion of this below the Append is fine, but I argue that
> the Vars above the Append should say "part", not "part_p1".
> In that way they'd look the same regardless of which partitions
> have been pruned or not.

So I've been thinking about how to make this actually happen.
I do not think it's possible without adding more information
to Plan trees. Which is not a show-stopper in itself --- there's
already various fields there that have no use except to support
EXPLAIN --- but it'd behoove us to minimize the amount of work
the planner spends to generate such new info.

I think it can be made to work with a design along these lines:

* Add the planner's AppendRelInfo list to the finished PlannedStmt.
We would have no need for the translated_vars list, only for the
recently-added reverse-lookup array, so we could reduce the cost
of copying plans by having setrefs.c zero out the translated_vars
fields, much as it does for unnecessary fields of RTEs.

* In Append and MergeAppend plan nodes, add a bitmapset field that
contains the relids of any inheritance parent rels formed by this
append operation. (It has to be a set, not a single relid, because
a partitioned join would form two appendrels at the same plan node.
In general, partitioned joins break a lot of the simpler ideas
I'd had before this one...) I think this is probably just the relids
of the path's parent RelOptInfo, so it's little or no extra cost to
calculate.

* In ExplainPreScanNode, treat relids mentioned in such fields as
referenced by the query, so that they'll be assigned aliases by
select_rtable_names_for_explain. (Note that this will generally mean
that a partition root table gets its unmodified alias, and all child
rels will have "_N" added, rather than the current situation where the
first unpruned child gets the parent's unmodified alias. This seems
good to me from a consistency standpoint, although it'll mean another
round of churn in the regression test results.)

* When ruleutils has to resolve a Var, and it descends through an
Append or MergeAppend that has this field nonempty, remember the
bitmapset of relevant relids as we continue recursing. Once we've
finally located a base Var, if the passed-down set of inheritance
relids isn't empty, then use the AppendRelInfo data to try to map
the base Var's varno/varattno back up to any one of these relids.
If successful, print the name of the mapped-to table and column
instead of the base Var's name.

This design will correctly print references to the "same" Var
differently depending on where they appear in the plan tree, ie above
or below the Append that forms the appendrel. I don't see any way we
can make that happen reliably without new plantree decoration --- in
particular, I don't think ruleutils can reverse-engineer which Appends
form which appendrels without any help.

An interesting point is what to do if we see more than one such append
node as we descend. We should union the sets of relevant appendrel
relids, for sure, but now there is a possibility that more than one
appendrel can be matched while chasing back up the AppendRelInfo data.
I think that can only happen for an inheritance appendrel nested
inside a UNION ALL appendrel, so the question becomes whether we'd
rather report the inheritance root or whatever alias we're going to
assign for UNION appendrels. Perhaps that choice should wait until
we've got some code to test these ideas with.

I haven't tried to code this yet, but will go do so if there aren't
objections to this sketch.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2019-12-04 15:54:53 Re: adding strndup
Previous Message Peter Eisentraut 2019-12-04 15:24:37 Re: Update minimum SSL version