pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: postgresql <postgres@lg.ndirect.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: efficient deletes on subqueries
Date: Tue, 30 Oct 2001 11:48:33 -0500
Message-ID: <20895.1004460513@sss.pgh.pa.us> (raw)
In-Reply-To: <9rmh7p$1e89$1@news.tht.net>
References: <9rmh7p$1e89$1@news.tht.net>

"postgresql" <postgres@lg.ndirect.co.uk> writes:
> I want to remove all entries from AA where keyA matches that from
> obsolete_AA_entries,
> i.e. SELECT * FROM AA NATURAL JOIN obsolete_AA_entries;

> DELETE FROM AA WHERE EXISTS(
>           SELECT * from obsolete_AA_entries o where AA.keyA = o.keyA);
> seems to be faster than
> DELETE FROM AA where (keyA) in (SELECT * from obsolete_AA_entries);

> However, both are sequentially going through AA which is huge rather than
> looking up values one by one from obsolete_AA_entries which is small.
> How do I persuade the database to change its query strategy?

Try
	DELETE FROM AA WHERE AA.keyA = obsolete_AA_entries.keyA;

This is not valid SQL according to the SQL standard, but Postgres takes
it anyway.

Turning sub-SELECT queries into plannable joins is on the todo list,
but it's not done yet :-(

			regards, tom lane



view thread (2+ messages)

Message-ID: <20895.1004460513@sss.pgh.pa.us>
Permalink:  ../20895.1004460513@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/20895.1004460513@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, postgres@lg.ndirect.co.uk
  Subject: Re: efficient deletes on subqueries
  In-Reply-To: <20895.1004460513@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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