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
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