pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Speedy query help..
2+ messages / 2 participants
[nested] [flat]

* Speedy query help..
@ 2000-04-10 20:36 Mitch Vincent <mitch@venux.net>
  2000-04-10 23:25 ` Re: Speedy query help.. Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Mitch Vincent @ 2000-04-10 20:36 UTC (permalink / raw)
  To: pgsql-sql

Any ideas on how I might speed this up? I know sub-selects are seriously
slow, I'm not sure what else can be done though.

DELETE from applicants_states WHERE app_id IN (SELECT s.app_id FROM
applicants_states AS s, applicants AS a WHERE s.app_id=a.app_id AND
(a.created + '90 days') < 'now' AND a.resubmitted < '10-03-1999')

Thanks!

- Mitch





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Speedy query help..
  2000-04-10 20:36 Speedy query help.. Mitch Vincent <mitch@venux.net>
@ 2000-04-10 23:25 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2000-04-10 23:25 UTC (permalink / raw)
  To: Mitch Vincent <mitch@venux.net>; +Cc: pgsql-sql

"Mitch Vincent" <mitch@venux.net> writes:
> Any ideas on how I might speed this up? I know sub-selects are seriously
> slow, I'm not sure what else can be done though.

> DELETE from applicants_states WHERE app_id IN (SELECT s.app_id FROM
> applicants_states AS s, applicants AS a WHERE s.app_id=a.app_id AND
> (a.created + '90 days') < 'now' AND a.resubmitted < '10-03-1999')

I believe you'd get the same result from

DELETE FROM applicants_states
WHERE app_id = applicants.app_id AND
      (applicants.created + '90 days') < 'now' AND
      applicants.resubmitted < '10-03-1999';

This is not SQL-standard; doing an implicit join when you mention
another table in WHERE is a leftover from Berkeley Postquel.  But
it solves this sort of problem rather handily.

If you want to stick to portable SQL, I'd at least suggest getting rid
of the unnecessary join in the subselect; wouldn't

DELETE from applicants_states WHERE app_id IN (SELECT app_id
    FROM applicants
    WHERE (created + '90 days') < 'now' AND resubmitted < '10-03-1999');

produce the same results?

Also, 7.0 does uncorrelated subselects (like this one) somewhat
faster than prior releases, so just upgrading might solve the problem
for you.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-04-10 23:25 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-10 20:36 Speedy query help.. Mitch Vincent <mitch@venux.net>
2000-04-10 23:25 ` Tom Lane <tgl@sss.pgh.pa.us>

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