From: | jboes(at)nexcerpt(dot)com (Jeff Boes) |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Long-running DELETE...WHERE... |
Date: | 2002-01-14 18:27:50 |
Message-ID: | d40a65a1.0201141027.6b15bc97@posting.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Why would a delete involving a subselect run so much longer than the
individual delete commands?
My situation: table A has 200,000 rows. I've made up a temporary table
which holds the single-valued primary key for 80,000 rows which I want
to delete.
DELETE FROM a WHERE id IN (select ID from tmp LIMIT 800);
runs for several minutes. But if I do
\o tmpfile
\t
SELECT 'DELETE FROM a WHERE id = ' || id || ';' from tmp limit 800;
\o
\i tmpfile
this completes in about 15 seconds, or 1/50 of the time for the
single-statement delete above.
In trying to optimize this process, I disabled all the relational
integrity triggers (foreign keys) involving the table, and then I
dropped all the indexes EXCEPT that of the primary key. All the
experiments were done within a single transaction using BEGIN. The
database version is 7.1.3, and the table was vacuumed very recently.
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2002-01-14 20:30:54 | Re: Commercial: New Book!! PostgreSQL book is released into |
Previous Message | Tom Lane | 2002-01-14 18:18:57 | Re: CREATE TABLE glitch -fix request for 7.2 |