pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrouble with explicit joins
3+ messages / 2 participants
[nested] [flat]
* Trouble with explicit joins
@ 2004-10-21 22:44 Dmitry Tkach <dmitry@openratings.com>
2004-10-21 23:32 ` Re: Trouble with explicit joins Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Dmitry Tkach @ 2004-10-21 22:44 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trouble with explicit joins
2004-10-21 22:44 Trouble with explicit joins Dmitry Tkach <dmitry@openratings.com>
@ 2004-10-21 23:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-21 23:36 ` Re: Trouble with explicit joins Dmitry Tkach <dmitry@openratings.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-10-21 23:32 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: pgsql-sql
Dmitry Tkach <dmitry@openratings.com> writes:
> 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,
It's not a "hint", it's a requirement. In general, changing the order
in which outer joins are executed changes the results.
There are some cases in which it is safe to rearrange the order, but
determining this takes close analysis of the join conditions, and we
don't (yet) have any code to do that. So the planner must be
conservative and take your join order as gospel.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trouble with explicit joins
2004-10-21 22:44 Trouble with explicit joins Dmitry Tkach <dmitry@openratings.com>
2004-10-21 23:32 ` Re: Trouble with explicit joins Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-10-21 23:36 ` Dmitry Tkach <dmitry@openratings.com>
0 siblings, 0 replies; 3+ messages in thread
From: Dmitry Tkach @ 2004-10-21 23:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane wrote:
>Dmitry Tkach <dmitry@openratings.com> writes:
>
>
>>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,
>>
>>
>
>It's not a "hint", it's a requirement. In general, changing the order
>in which outer joins are executed changes the results.
>
>There are some cases in which it is safe to rearrange the order, but
>determining this takes close analysis of the join conditions, and we
>don't (yet) have any code to do that. So the planner must be
>conservative and take your join order as gospel.
>
> regards, tom lane
>
>
Yeah.. that's what I figured.
Are you saying there is no way around it at all? Isn't there a syntax
supported to write a left join with implicit joins?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-10-21 23:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-21 22:44 Trouble with explicit joins Dmitry Tkach <dmitry@openratings.com>
2004-10-21 23:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-21 23:36 ` Dmitry Tkach <dmitry@openratings.com>
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