Skip site navigation (1) Skip section navigation (2)

Re: 8.3.1 query plan

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Steve Clark <sclark(at)netwolves(dot)com>
Cc: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: Re: 8.3.1 query plan
Date: 2008-08-28 15:01:18
Message-ID: 10650.1219935678@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Steve Clark <sclark(at)netwolves(dot)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

In response to

Responses

pgsql-general by date

Next:From: Adrian KlaverDate: 2008-08-28 15:04:39
Subject: Re: pg_dump problem
Previous:From: Jaime CasanovaDate: 2008-08-28 14:58:17
Subject: Re: Vaccuum best practice: cronjob or autovaccuum?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group