From: Tom Lane <tgl@sss.pgh.pa.us>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: David Rowley <david.rowley@2ndquadrant.com>
Cc: Imai, Yoshikazu <imai.yoshikazu@jp.fujitsu.com>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: speeding up planning with partitions
Date: Tue, 19 Feb 2019 15:57:53 -0500
Message-ID: <30961.1550609873@sss.pgh.pa.us> (raw)
In-Reply-To: <1002.1550596071@sss.pgh.pa.us>
References: <8ea8bbb1-31ff-a469-02a7-281c3eba4bea@lab.ntt.co.jp>
<201902011001.4v25uw6w6kb5@alvherre.pgsql>
<CAKJS1f9LkQ=w4cq2_OaW05=mw8H_jQofWHiK0w3VVP-nSkUUmA@mail.gmail.com>
<CA+HiwqFioAonMAgE0OWZeK=NEEOKmq-c=iYR2pkPOO=q6NxMkg@mail.gmail.com>
<e85c4b41-80d6-1045-d934-feeca4a463ab@lab.ntt.co.jp>
<0F97FA9ABBDBE54F91744A9B37151A5127865E@g01jpexmbkw24>
<5d00eb35-375d-0672-0626-96bdb86829d0@lab.ntt.co.jp>
<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>
I wrote:
> OK, I'll make another pass over 0001 today.
So I started the day with high hopes for this, but the more I looked at
it the less happy I got, and finally I ran into something that looks to
be a complete show-stopper. Namely, that the patch does not account
for the possibility of an inherited target rel being the outside for a
parameterized path to some other rel. Like this example in the
regression database:
explain update a set aa = aa + 1
from tenk1 t where a.aa = t.unique2;
With HEAD, you get a perfectly nice plan that consists of an append
of per-child plans like this:
-> Nested Loop (cost=0.29..8.31 rows=1 width=16)
-> Seq Scan on a (cost=0.00..0.00 rows=1 width=10)
-> Index Scan using tenk1_unique2 on tenk1 t (cost=0.29..8.30 rows=1
width=10)
Index Cond: (unique2 = a.aa)
With the 0001 patch, this gets an Assert during set_base_rel_pathlists,
because indxpath.c tries to make a parameterized path for tenk1
with "a" as the outer rel. Since tenk1's joinlist hasn't been
touched, it's still referencing the inheritance parent, and the
code notices that we haven't made a rowcount estimate for that.
Even if we had, we'd generate a Path referencing Vars of the parent
rel, which would not work.
Conceivably, such a Path could be fixed up later (say by applying
adjust_appendrel_attrs to it during createplan.c), but that is not
going to fix the fundamental problem: the cost estimate for such a
Path should vary depending on how large we think the outer rel is,
and we don't have a reasonable way to set that if we're trying to
make a one-size-fits-all Path for something that's being joined to
an inheritance tree with a widely varying set of relation sizes.
So I do not see any way to make this approach work without a
significant(?) sacrifice in the quality of plans.
I've got other issues with the patch too, but it's probably not
worth getting into them unless we can answer this objection.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, Langote_Amit_f8@lab.ntt.co.jp, david.rowley@2ndquadrant.com, imai.yoshikazu@jp.fujitsu.com, amitlangote09@gmail.com, alvherre@2ndquadrant.com
Subject: Re: speeding up planning with partitions
In-Reply-To: <30961.1550609873@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox