Trouble with explicit joins
am 22.10.2004 00:44:23 von DmitryHi, everybody!
I am writing a fairly long query, that joins several (like 10-15)
tables. It is dynamically generated, and the tables in the join may be
different, depending on the criteria etc...
The problem is that I need to outer (left) join some of those tables.
The only way I know to do that is using explicit join sytax ("select
blah from foo left join bar using (x)").
BUT when I try to write my query with that syntax, postgres comes up
with a totally wrong query plan (e.g., seq scan on a table with 100
million rows), even when no outer joins are used at all
(as I said, the query is dynamic - sometimes it needs a left join,
sometimes it does not, but it is the same code that generates it).
The same exact query with implicit join syntax ("select blah from foo,
bar where foo.x=bar.x" works fine.
I suppose, this is because the planner takes the order, in which the
tables appear in the explicit joins as some kind of a hint to how I want
that query to be executed, and, if I changed the order o fthose joins, I
believe, I would be able to get the same query plan as without explicit
joins, but unfortunately this is not an option, since, as I said, the
query is dynamically generated the set of tables being joined is
different every time, the criteria varies too, there are just too many
possibilities.
So, my only hope is that, perhaps, I am missing something simple here,
and somebody would be able to tell me either how to do an outer join
with implicit syntax or how to make the planner behave the same way when
it reads explicit syntax.
I'd appreciate any ideas.
Thanks a lot!
Dima
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly