[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] efficient deletes on subqueries
From: Tom Lane <tgl () sss ! pgh ! pa ! us>
Date: 2001-10-30 16:48:33
[Download RAW message or body]
"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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic