Re: [GENERAL] 8.3.1 query plan

Steve Clark <sclark@netwolves.com> writes:
> Tom Lane wrote:
>> Consider testing the conditions on A at the top level, instead of
>> redundantly checking them inside the sub-query on B.

> Thanks for the response Tom, I am a SQL neophyte, so I'll try to
> rework the query.

What I meant to suggest was just

explain insert into myevents select * from t_unit_event_log a where
a.event_status = 1 and a.event_ref_log_no IS NOT NULL
and a.event_log_no not in (select event_log_no from myevents)
and exists (select b.event_log_no from myevents b
where a.event_ref_log_no = b.event_log_no)

ie, pull everything out of the subquery that doesn't depend on B.

Although, looking at it in this form, it seems like you'd be well
advised to then replace the EXISTS with an IN:

... and a.event_ref_log_no in (select b.event_log_no from myevents b)

Although those two forms should be equivalent, reality is that current
releases of PG are generally smarter about optimizing IN than EXISTS.
(The difference should largely go away as of 8.4.)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general