Trouble with explicit joins
От | Dmitry Tkach |
---|---|
Тема | Trouble with explicit joins |
Дата | |
Msg-id | 41783BC7.4040207@openratings.com обсуждение исходный текст |
Ответы |
Re: Trouble with explicit joins
|
Список | pgsql-sql |
Hi, 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
В списке pgsql-sql по дате отправления: