From: Dmitry Tkach <dmitry@openratings.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Trouble with explicit joins
Date: Thu, 21 Oct 2004 18:44:23 -0400
Message-ID: <41783BC7.4040207@openratings.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dmitry@openratings.com
Subject: Re: Trouble with explicit joins
In-Reply-To: <41783BC7.4040207@openratings.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox