Re: [HACKERS] Runtime Partition Pruning - Mailing list pgsql-hackers

On 5 April 2018 at 15:14, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
> On 2018/03/31 22:52, David Rowley wrote:
>> Initialising
>> Append/MergeAppend/ModifyTable nodes with fewer subnodes than were
>> originally planned did require a small change in explain.c in some
>> code that was assuming the subnode arrays were the same length as the
>> subplan list. I also ended up adding a Int property to EXPLAIN to show
>> the number of subnodes that have been removed during execution.
>> Unfortunately, there is a small corner case with this in the case
>> where all subnodes are removed it leaves EXPLAIN with no subnodes to
>> search for outer side Vars in. I didn't really see any sane way to
>> handle this in EXPLAIN, so I think the best fix for this is what I've
>> done, and that's just to always initalise at least 1 subnode, even
>> when none match the external Params. Cost-wise, I don't think this is
>> such a big deal as the cost savings here are coming from saving on
>> initalising ten's or hundreds of subnodes, not 1.
>
> I have wondered about the possibility of setting a valid (non-dummy)
> targetlist in Append and MergeAppend if they're created for a partitioned
> table.  Currently they're overwritten by a dummy one using
> set_dummy_tlist_references() in set_plan_refs() citing the following reason:
>
>  * set_dummy_tlist_references
>  *    Replace the targetlist of an upper-level plan node with a simple
>  *    list of OUTER_VAR references to its child.
>  *
>  * This is used for plan types like Sort and Append that don't evaluate
>  * their targetlists.  Although the executor doesn't care at all what's in
>  * the tlist, EXPLAIN needs it to be realistic.
>
> In fact, when I had noticed that this EXPLAIN behavior I had wondered if
> that's something we should have discussed when d3cc37f1d801a [1] went in.

I had a quick hack at this to see if it would work and it does seem to
on my very simple test. However, it would mean removing
set_dummy_tlist_references from more than just Append/MergeAppend

create table listp (a int, b int) partition by list(a);
create table listp1 partition of listp for values in(1);
create table listp2 partition of listp for values in(2);
prepare q1 (int, int) as select * from listp where a in($1,$2) order
by b limit 1;
explain execute q1(1,2);
explain execute q1(1,2);
explain execute q1(1,2);
explain execute q1(1,2);
explain execute q1(1,2);
explain (verbose, costs off) execute q1(0,0);
                       QUERY PLAN
--------------------------------------------------------
 Limit
   Output: listp.a, listp.b
   ->  Sort
         Output: listp.a, listp.b
         Sort Key: listp.b
         ->  Append
               Subplans Pruned: 2
(7 rows)

The downside is that if we were to do this it would mean changing the
output in cases like:

explain (verbose, costs off) (select a z, b y from listp union all
select * from listp) order by y;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Sort
   Output: z, y
   Sort Key: y
   ->  Append
         ->  Seq Scan on public.listp1
               Output: listp1.a, listp1.b
         ->  Seq Scan on public.listp2
               Output: listp2.a, listp2.b
         ->  Seq Scan on public.listp1 listp1_1
               Output: listp1_1.a, listp1_1.b
         ->  Seq Scan on public.listp2 listp2_1
               Output: listp2_1.a, listp2_1.b

Notice the sort key now refers to the alias rather than a column from
the first append child.

It sure is an interesting thought, and one I'd not considered, but I
don't think trying for something like this is going to be the path of
least resistance. It may also add quite a bit of complexity if we just
try to do it when the OUTER_VAR would lead to a Append/MergeAppend
which belongs to a partitioned table scan.

I think this idea has good merit and some form of it might be the
nicest way to allow run-time pruning of all subnodes in the future.
Perhaps we can put it on the shelf and think about it again for PG12.
However, it might not be the most interesting optimization to work on,
as I think probably run-time pruning away of all subnodes is probably
far less common than pruning some, or all but one, and the cost of
initializing the one unneeded subnode is not so big.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services