On 27/02/2015 19:45, Tom Lane wrote:
I wrote:
I had actually thought that we'd fixed this type of problem in recent
versions, and that you should be able to get a plan that would look like
Nestloop
-> scan dim1
-> Nestloop
-> scan dim2
-> indexscan fact table using dim1.a and dim2.b
After closer study, I think this is an oversight in commit
e2fa76d80ba571d4de8992de6386536867250474, which quoth
+It can be useful for the parameter value to be passed down through
+intermediate layers of joins, for example:
+
+ NestLoop
+ -> Seq Scan on A
+ Hash Join
+ Join Condition: B.Y = C.W
+ -> Seq Scan on B
+ -> Index Scan using C_Z_IDX on C
+ Index Condition: C.Z = A.X
+
+If all joins are plain inner joins then this is unnecessary, because
+it's always possible to reorder the joins so that a parameter is used
+immediately below the nestloop node that provides it. But in the
+presence of outer joins, join reordering may not be possible, and then
+this option can be critical. Before version 9.2, Postgres used ad-hoc
This reasoning overlooked the fact that if we need parameters from
more than one relation, and there's no way to join those relations
to each other directly, then we have to allow passing the dim1 parameter
down through the join to dim2.
The attached patch seems to fix it (modulo the need for some updates
in the README, and maybe a regression test). Could you see if this
produces satisfactory plans for you?
From what I see, it's just perfect. I'll give it a more thorough look a
bit later, but it seems to be exactly what I was waiting for.
Thanks a lot.
Regards
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
