Re: Badly planned queries with JOIN syntax - Mailing list pgsql-general

On Fri, 4 Apr 2003, Phil Mayers wrote:

> I have a requirement for some rather complex multi-table queries involving
> inner, outer and full joins. However, I'm running into some problems because the
> planner always JOINs in the order I give them (as documented) - which is not the
> optimal plan. The query is (very) dynamically generated, so it's not as simple
> as "order the JOINs right" because there are some 40,000 possible queries (and
> that's just with the current data and table set).
>
> What I would like to do is push all JOIN constraints down into a WHERE clause,
> and for INNER joins specified this way the planner seems to generate the optimal
> query each time (since it has freedom to re-order). However, under Postgresql,
> I'm not aware of any way of doing OUTER joins with a WHERE clause (I believe
> ANSI SQL92 had a "table.column *= otable.ocolumn" which equates to "table LEFT
> OUTER JOIN otable on column=ocolumn").

AFAICT SQL92 has no such syntax. It's hard to do without alot of
limitations (because join order can be important to the results of the
query with outer joins).

> create table a (id text, somedata text, somedata2 text, primary key (id));
> create table b (id text, pid text not null, extradata text, primary key (id));
> create index b_pid on b(pid);
> create table c (id text, pid textnot null, moredata text, primary key (id));
> create index c_pid on c(pid);
>
> a, b, c contain tens of thousands of rows. The search function can search on any
> field, but if the user searches on "moredata", you can do:
>
> select * from a join b on b.pid = a.id join c on c.pid = b.id where
> moredata like 'blah%';
> select * from a join b on b.pid = a.id join c on c.pid = b.id where
> a.somedata like 'foo%';

> Put another way - I *don't* want to use the order of the JOINs as an explicit
> command to the planner, but *do* need to use the JOIN syntax since I need OUTER
> and FULL joins in some or all queries (which you can's specify with WHERE).

As an example, where would the outer join go in the above?  Would it be in
place of one of the joins there or to another table entirely?  If the
latter, you might be able to do a subselct in from that contains the inner
join portion using the from table,table where ... syntax and outer join
that to the necessary tables.  I think the planner is probably smart
enough to do something reasonable with that.