Re: speeding up planning with partitions

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: "Imai, Yoshikazu" <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com>
Cc: "'Amit Langote'" <amitlangote09(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Justin Pryzby <pryzby(at)telsasoft(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: speeding up planning with partitions
Date: 2019-03-01 13:01:31
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


Thanks for testing and sorry it took me a while to reply.

On 2019/02/25 15:24, Imai, Yoshikazu wrote:
> [update_pt_with_joining_another_pt.sql]
> update rt set c = jrt.c + 100 from jrt where rt.b = jrt.b;
> [pgbench]
> pgbench -n -f update_pt_with_joining_another_pt_for_ptkey.sql -T 60 postgres
> [results]
> (part_num_rt, part_num_jrt) master patched(0001)
> --------------------------- ------ -------------
> (3, 1024) 8.06 5.89
> (3, 2048) 1.52 0.87
> (6, 1024) 4.11 1.77
> With HEAD, we add target inheritance and source inheritance to parse->rtable in inheritance_planner and copy and adjust it for child tables at beginning of each planning of child tables.
> With the 0001 patch, we add target inheritance to parse->rtable in inheritance_planner and add source inheritance to parse->rtable in make_one_rel(under grouping_planner()) during each planning of child tables.
> Adding source inheritance to parse->rtable may be the same process between each planning of child tables and it might be useless. OTOH, from the POV of making inheritance-expansion-at-bottom better, expanding source inheritance in make_one_rel seems correct design to me.
> How should we do that...?

To solve this problem, I ended up teaching inheritance_planner to reuse
the objects for *source* inheritance child relations (RTEs,
AppendRelInfos, and PlanRowMarks) created during the planning of the 1st
child query for the planning of subsequent child queries. Set of source
child relations don't change between different planning runs, so it's okay
to do so. Of course, I had to make sure that query_planner (which is not
in the charge of adding source inheritance child objects) can notice that.

Please find attached updated patches. Will update source code comments,
commit message and perform other fine-tuning over the weekend if possible.


Attachment Content-Type Size
v25-0001-Lazy-creation-of-RTEs-for-inheritance-children.patch text/plain 97.2 KB
v25-0002-Teach-planner-to-only-process-unpruned-partition.patch text/plain 7.1 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2019-03-01 13:03:45 Re: speeding up planning with partitions
Previous Message Michael Meskes 2019-03-01 13:01:07 Re: SQL statement PREPARE does not work in ECPG