David Leverton <levertond@xxxxxxxxxxxxxx> writes:
> On 7 March 2013 05:52, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
>> Josh Berkus <josh@xxxxxxxxxxxx> writes:
>>> Actually, in case #4, Postgres *is* pushing down the join qual into the
>>> segments of the Union.
>> Yeah, but not further. I believe the core issue here (as of 9.2) is
>> that we're not willing to generate parameterized paths for subquery
>> relations. We could do that without a huge amount of new code,
>> I think, but the scary thing is how much time it might take to generate
>> (and then discard most of the) plans for assorted parameterizations of
>> complicated subqueries.
> Thanks for looking at this, both of you.
> Does "as of 9.2" mean it's better in 9.3?
No, I meant it was worse before 9.2 --- previous versions weren't even
theoretically capable of generating the plan shape you want. What
you're after is for the sub-join to be treated as a parameterized
sub-plan, and we did not have any ability to do that for anything more
complicated than a single-relation scan.
> I do intend to upgrade once
> it's released, so if it can handle this better (or if there's anything
> that can be done to improve it between now and then without making
> other things worse) that would be great. Otherwise, I'm wondering if
> the addition of LATERAL will help persuade the planner to do what I
> want, something like this, perhaps?
Good idea, but no such luck in that form: it's still not going to try to
push the parameterization down into the sub-query. I think you'd have
to write out the query with the views expanded and manually put the
WHERE restrictions into the lowest join level. [ experiments... ]
Looks like only the UNION view has to be manually expanded to get a
good plan with HEAD:
regression=# explain SELECT *
FROM item_reference,
LATERAL (
SELECT item_id_a, item_id_b FROM bundled_item WHERE (item_id_a, item_id_b)
= (item_reference.item_id_a, item_reference.item_id_b)
UNION ALL
SELECT item_id_a, item_id_b FROM unbundled_item WHERE (item_id_a, item_id_b)
= (item_reference.item_id_a, item_reference.item_id_b)
) item
WHERE reference_id = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.57..25.99 rows=2 width=20)
-> Seq Scan on item_reference (cost=0.00..1.02 rows=1 width=12)
Filter: (reference_id = 1)
-> Append (cost=0.57..24.94 rows=2 width=8)
-> Nested Loop (cost=0.57..16.61 rows=1 width=8)
Join Filter: (bundle.bundle_type = bundle_contents.bundle_type)
-> Index Scan using bundle_pkey on bundle (cost=0.29..8.31 rows=1 width=8)
Index Cond: (bundle_id = item_reference.item_id_a)
-> Index Scan using bundle_contents_pkey on bundle_contents (cost=0.28..8.29 rows=1 width=8)
Index Cond: (item_id = item_reference.item_id_b)
-> Index Only Scan using unbundled_item_pkey on unbundled_item (cost=0.29..8.31 rows=1 width=8)
Index Cond: ((item_id_a = item_reference.item_id_a) AND (item_id_b = item_reference.item_id_b))
(12 rows)
You might be able to accomplish something similar without LATERAL, if
you're willing to give up the notational convenience of the views.
Don't have time right now to experiment further though.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
