Reply-To: jesper.pedersen@redhat.com
Subject: Re: speeding up planning with partitions
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>,
 'Amit Langote' <amitlangote09@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 David Rowley <david.rowley@2ndquadrant.com>,
 Alvaro Herrera <alvherre@2ndquadrant.com>,
 Justin Pryzby <pryzby@telsasoft.com>,
 Pg Hackers <pgsql-hackers@postgresql.org>
References: 
 <CAKJS1f9XAePd2+8F4=wtHKdDdckgKcSoXb-ZbU8Wbt86mhLxKA@mail.gmail.com>
 <b8a139b8-2f5d-6079-67b2-99957af3fc36@lab.ntt.co.jp>
 <7453.1550518972@sss.pgh.pa.us>
 <0a70234e-b2e6-debc-2fb5-e8fd927caa00@lab.ntt.co.jp>
 <1002.1550596071@sss.pgh.pa.us> <30961.1550609873@sss.pgh.pa.us>
 <250c61bc-479c-92f4-d19d-d768e3bcdcbc@lab.ntt.co.jp>
 <13510.1550677844@sss.pgh.pa.us>
 <a47e6214-bd46-ab4b-2f84-f9f726e04642@lab.ntt.co.jp>
 <35b02675-83c2-a12b-961a-fec249b06f35@lab.ntt.co.jp>
 <20190222165932.GG28750@telsasoft.com>
 <CA+HiwqEUwfg4s=4=is-+7k-WM-2Gs9K3iFB96QNm_eafcBozqQ@mail.gmail.com>
 <0F97FA9ABBDBE54F91744A9B37151A51293422@g01jpexmbkw24>
 <8098beec-fc04-303d-55b9-73c3d94ba1ea@lab.ntt.co.jp>
 <b5362c29-cca1-de7b-13d0-9e51f9911185@lab.ntt.co.jp>
 <80afef3f-1870-5cb3-eeaf-7d8ecb41994c@lab.ntt.co.jp>
 <af3da198-e4aa-0535-8b13-40b02a4a7c41@lab.ntt.co.jp>
From: Jesper Pedersen <jesper.pedersen@redhat.com>
Organization: Red Hat
Message-ID: <d768d90d-628e-6cdc-513d-f50a0ce02cea@redhat.com>
Date: Tue, 12 Mar 2019 09:23:25 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.4.0
MIME-Version: 1.0
In-Reply-To: <af3da198-e4aa-0535-8b13-40b02a4a7c41@lab.ntt.co.jp>
Content-Type: multipart/mixed;
 boundary="------------A9864800F19A99C1FB2CE2AB"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------A9864800F19A99C1FB2CE2AB
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi Amit,

On 3/12/19 4:22 AM, Amit Langote wrote:
> I wrestled with this idea a bit and concluded that we don't have to
> postpone *all* of preprocess_targetlist() processing to query_planner,
> only the part that adds row mark "junk" Vars, because only those matter
> for the problem being solved.  To recap, the problem is that delaying
> adding inheritance children (and hence their row marks if any) means we
> can't add "junk" columns needed to implement row marks, because which ones
> to add is not clear until we've seen all the children.
> 
> I propose that we delay the addition of "junk" Vars to query_planner() so
> that it doesn't stand in the way of deferring inheritance expansion to
> query_planner() too.  That means the order of reltarget expressions for
> row-marked rels will change, but I assume that's OK.  At least it will be
> consistent for both non-inherited baserels and inherited ones.
> 
> Attached updated version of the patches with the above proposal
> implemented by patch 0002.  To summarize, the patches are as follows:
> 
> 0001: make building of "other rels" a separate step that runs after
> deconstruct_jointree(), implemented by a new subroutine of query_planner
> named add_other_rels_to_query()
> 
> 0002: delay adding "junk" Vars to after add_other_rels_to_query()
> 
> 0003: delay inheritance expansion to add_other_rels_to_query()
> 
> 0004, 0005: adjust inheritance_planner() to account for the fact that
> inheritance is now expanded by query_planner(), not subquery_planner()
> 
> 0006: perform partition pruning while inheritance is being expanded,
> instead of during set_append_append_rel_size()
> 
> 0007: add a 'live_parts' field to RelOptInfo to store partition indexes
> (not RT indexes) of unpruned partitions, which speeds up looping over
> part_rels array of the partitioned parent
> 
> 0008: avoid copying PartitionBoundInfo struct for planning
> 

After applying 0004 check-world fails with the attached. CFBot agrees [1].

[1] https://travis-ci.org/postgresql-cfbot/postgresql/builds/505107884

Best regards,
  Jesper


--------------A9864800F19A99C1FB2CE2AB
Content-Type: text/plain; charset=UTF-8;
 name="regression.diff.txt"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="regression.diff.txt"

ZGlmZiAtVTMgL2hvbWUvanBlZGVyc2VuL1Bvc3RncmVTUUwvcG9zdGdyZXNxbC9jb250cmli
L3Bvc3RncmVzX2Zkdy9leHBlY3RlZC9wb3N0Z3Jlc19mZHcub3V0IC9ob21lL2pwZWRlcnNl
bi9Qb3N0Z3JlU1FML3Bvc3RncmVzcWwvY29udHJpYi9wb3N0Z3Jlc19mZHcvcmVzdWx0cy9w
b3N0Z3Jlc19mZHcub3V0Ci0tLSAvaG9tZS9qcGVkZXJzZW4vUG9zdGdyZVNRTC9wb3N0Z3Jl
c3FsL2NvbnRyaWIvcG9zdGdyZXNfZmR3L2V4cGVjdGVkL3Bvc3RncmVzX2Zkdy5vdXQJMjAx
OS0wMy0xMiAwNzo0NjowOC40MzA2OTAyMjkgLTA0MDAKKysrIC9ob21lL2pwZWRlcnNlbi9Q
b3N0Z3JlU1FML3Bvc3RncmVzcWwvY29udHJpYi9wb3N0Z3Jlc19mZHcvcmVzdWx0cy9wb3N0
Z3Jlc19mZHcub3V0CTIwMTktMDMtMTIgMDc6NTk6MDEuMTM0Mjg1MTU5IC0wNDAwCkBAIC03
MTkwLDggKzcxOTAsOCBAQAogLS0gQ2hlY2sgVVBEQVRFIHdpdGggaW5oZXJpdGVkIHRhcmdl
dCBhbmQgYW4gaW5oZXJpdGVkIHNvdXJjZSB0YWJsZQogZXhwbGFpbiAodmVyYm9zZSwgY29z
dHMgb2ZmKQogdXBkYXRlIGJhciBzZXQgZjIgPSBmMiArIDEwMCB3aGVyZSBmMSBpbiAoc2Vs
ZWN0IGYxIGZyb20gZm9vKTsKLSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgUVVFUlkgUExBTiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgIAotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCisgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIFFVRVJZIFBMQU4gICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAKKy0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLQogIFVwZGF0ZSBvbiBwdWJsaWMuYmFyCiAgICBVcGRhdGUgb24gcHVibGljLmJh
cgogICAgRm9yZWlnbiBVcGRhdGUgb24gcHVibGljLmJhcjIKQEAgLTcyMTQsMjIgKzcyMTQs
MjIgQEAKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIE91dHB1dDogZm9vMi5m
MSwgZm9vMi5jdGlkLCBmb28yLiosIGZvbzIudGFibGVvaWQKICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgIFJlbW90ZSBTUUw6IFNFTEVDVCBmMSwgZjIsIGYzLCBjdGlkIEZS
T00gcHVibGljLmxvY3QxCiAgICAtPiAgSGFzaCBKb2luCi0gICAgICAgICBPdXRwdXQ6IGJh
cjIuZjEsIChiYXIyLmYyICsgMTAwKSwgYmFyMi5mMywgYmFyMi5jdGlkLCBmb28uY3RpZCwg
Zm9vLiosIGZvby50YWJsZW9pZAorICAgICAgICAgT3V0cHV0OiBiYXIyLmYxLCAoYmFyMi5m
MiArIDEwMCksIGJhcjIuZjMsIGJhcjIuY3RpZCwgZm9vLmN0aWQsIGZvby4qCiAgICAgICAg
ICBJbm5lciBVbmlxdWU6IHRydWUKICAgICAgICAgIEhhc2ggQ29uZDogKGJhcjIuZjEgPSBm
b28uZjEpCiAgICAgICAgICAtPiAgRm9yZWlnbiBTY2FuIG9uIHB1YmxpYy5iYXIyCiAgICAg
ICAgICAgICAgICBPdXRwdXQ6IGJhcjIuZjEsIGJhcjIuZjIsIGJhcjIuZjMsIGJhcjIuY3Rp
ZAogICAgICAgICAgICAgICAgUmVtb3RlIFNRTDogU0VMRUNUIGYxLCBmMiwgZjMsIGN0aWQg
RlJPTSBwdWJsaWMubG9jdDIgRk9SIFVQREFURQogICAgICAgICAgLT4gIEhhc2gKLSAgICAg
ICAgICAgICAgIE91dHB1dDogZm9vLmYxLCBmb28uY3RpZCwgZm9vLiosIGZvby50YWJsZW9p
ZAorICAgICAgICAgICAgICAgT3V0cHV0OiBmb28uZjEsIGZvby5jdGlkLCBmb28uKgogICAg
ICAgICAgICAgICAgLT4gIEhhc2hBZ2dyZWdhdGUKLSAgICAgICAgICAgICAgICAgICAgIE91
dHB1dDogZm9vLmYxLCBmb28uY3RpZCwgZm9vLiosIGZvby50YWJsZW9pZAorICAgICAgICAg
ICAgICAgICAgICAgT3V0cHV0OiBmb28uZjEsIGZvby5jdGlkLCBmb28uKgogICAgICAgICAg
ICAgICAgICAgICAgR3JvdXAgS2V5OiBmb28uZjEKICAgICAgICAgICAgICAgICAgICAgIC0+
ICBBcHBlbmQKICAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiBw
dWJsaWMuZm9vCi0gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBPdXRwdXQ6IGZv
by5mMSwgZm9vLmN0aWQsIGZvby4qLCBmb28udGFibGVvaWQKKyAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgIE91dHB1dDogZm9vLmYxLCBmb28uY3RpZCwgZm9vLioKICAgICAg
ICAgICAgICAgICAgICAgICAgICAgIC0+ICBGb3JlaWduIFNjYW4gb24gcHVibGljLmZvbzIK
LSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIE91dHB1dDogZm9vMi5mMSwgZm9v
Mi5jdGlkLCBmb28yLiosIGZvbzIudGFibGVvaWQKKyAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgIE91dHB1dDogZm9vMi5mMSwgZm9vMi5jdGlkLCBmb28yLioKICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgIFJlbW90ZSBTUUw6IFNFTEVDVCBmMSwgZjIsIGYz
LCBjdGlkIEZST00gcHVibGljLmxvY3QxCiAoMzkgcm93cykKIAo=
--------------A9864800F19A99C1FB2CE2AB--