From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alex Reece <awreece@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Bad plan chosen for union all
Date: Tue, 28 Nov 2017 23:43:14 -0500
Message-ID: <4237.1511930594@sss.pgh.pa.us> (raw)
In-Reply-To: <CANywC6CpaHnM7dawEu3M_X+GUwCOGUAJQoquZP7-aoMbw2SXQw@mail.gmail.com>
References: <CANywC6DvyXn1bMuo7+UAKvMKaCk9vvD7ez5WPS-t+bAc3U7btQ@mail.gmail.com>
<CANywC6CpaHnM7dawEu3M_X+GUwCOGUAJQoquZP7-aoMbw2SXQw@mail.gmail.com>
Alex Reece <awreece@gmail.com> writes:
> I managed to reduce my test case: the following query does not take
> advantage of the index on contribution metrics.
Yeah. What you're wishing is that the planner would push a join
condition down into a subquery, but it won't do that at present.
Doing so would require generating "parameterized paths" for subqueries.
While I do not think there's any fundamental technical reason anymore
that we couldn't do so, there's considerable risk of wasting a lot of
planner cycles chasing unprofitable plan alternatives. Anyway it was
totally impractical before 9.6's upper-planner-pathification changes,
and not all of the dust has settled from that rewrite.
> But I expected it to be equivalent to the plan from this query:
The difference here is that, from the perspective of the outer query,
the WHERE condition is a restriction clause on the "cim" relation,
not a join clause. So it will get pushed down into the subquery
without creating any join order constraints on the outer query.
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, awreece@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Bad plan chosen for union all
In-Reply-To: <4237.1511930594@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