pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: postgresql <postgres@lg.ndirect.co.uk>
To: pgsql-sql@postgresql.org
Subject: efficient deletes on subqueries
Date: Tue, 30 Oct 2001 15:33:05 -0000
Message-ID: <9rmh7p$1e89$1@news.tht.net> (raw)

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





view thread (2+ messages)  latest in thread

Message-ID: <9rmh7p$1e89$1@news.tht.net>
Permalink:  ../9rmh7p$1e89$1@news.tht.net/
Also on:    postgresql.org/message-id/9rmh7p$1e89$1@news.tht.net

 · 

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: postgres@lg.ndirect.co.uk
  Subject: Re: efficient deletes on subqueries
  In-Reply-To: <9rmh7p$1e89$1@news.tht.net>

* 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