pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedefficient deletes on subqueries
2+ messages / 2 participants
[nested] [flat]
* efficient deletes on subqueries
@ 2001-10-30 15:33 postgresql <postgres@lg.ndirect.co.uk>
2001-10-30 16:48 ` Re: efficient deletes on subqueries Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: postgresql @ 2001-10-30 15:33 UTC (permalink / raw)
To: pgsql-sql
I have a very large table with > 1 million entries and I wish to delete rows
which match any entries in a second table.
What is the most efficient way of doing this?
CREATE TABLE AA (keyA integer NOT NULL, info TEXT);
CREATE TABLE obsolete_AA_entries (keyA integer NOT NULL);
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;
Both of the tables are UNIQUE indiced on keyA.
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?
Thanks a lot
Llew
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: efficient deletes on subqueries
2001-10-30 15:33 efficient deletes on subqueries postgresql <postgres@lg.ndirect.co.uk>
@ 2001-10-30 16:48 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2001-10-30 16:48 UTC (permalink / raw)
To: postgresql <postgres@lg.ndirect.co.uk>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-10-30 16:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-30 15:33 efficient deletes on subqueries postgresql <postgres@lg.ndirect.co.uk>
2001-10-30 16:48 ` 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