pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: jboes@nexcerpt.com
To: pgsql-sql@postgresql.org
Subject: Long-running DELETE...WHERE...
Date: 14 Jan 2002 10:27:50 -0800
Message-ID: <d40a65a1.0201141027.6b15bc97@posting.google.com> (raw)

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.



view thread (5+ messages)  latest in thread

Message-ID: <d40a65a1.0201141027.6b15bc97@posting.google.com>
Permalink:  ../d40a65a1.0201141027.6b15bc97@posting.google.com/
Also on:    postgresql.org/message-id/d40a65a1.0201141027.6b15bc97@posting.google.com

 · 

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: jboes@nexcerpt.com
  Subject: Re: Long-running DELETE...WHERE...
  In-Reply-To: <d40a65a1.0201141027.6b15bc97@posting.google.com>

* 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