Message-ID: <41783BC7.4040207@openratings.com>
Date: Thu, 21 Oct 2004 18:44:23 -0400
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040113
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Trouble with explicit joins
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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