From: Craig Ringer <craig@postnewspapers.com.au>
To: Jessica Richard <rjessil@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow delete
Date: Fri, 04 Jul 2008 13:16:31 +0800
Message-ID: <486DB22F.10106@postnewspapers.com.au> (raw)
In-Reply-To: <357892.18257.qm@web56412.mail.re3.yahoo.com>
References: <357892.18257.qm@web56412.mail.re3.yahoo.com>
Jessica Richard wrote:
> I have a table with 29K rows total and I need to delete about 80K out of it.
I assume you meant 290K or something.
> I have a b-tree index on column cola (varchar(255) ) for my where clause
> to use.
>
> my "select count(*) from test where cola = 'abc' runs very fast,
>
> but my actual "delete from test where cola = 'abc';" takes forever,
> never can finish and I haven't figured why....
When you delete, the database server must:
- Check all foreign keys referencing the data being deleted
- Update all indexes on the data being deleted
- and actually flag the tuples as deleted by your transaction
All of which takes time. It's a much slower operation than a query that
just has to find out how many tuples match the search criteria like your
SELECT does.
How many indexes do you have on the table you're deleting from? How many
foreign key constraints are there to the table you're deleting from?
If you find that it just takes too long, you could drop the indexes and
foreign key constraints, do the delete, then recreate the indexes and
foreign key constraints. This can sometimes be faster, depending on just
what proportion of the table must be deleted.
Additionally, remember to VACUUM ANALYZE the table after that sort of
big change. AFAIK you shouldn't really have to if autovacuum is doing
its job, but it's not a bad idea anyway.
--
Craig Ringer
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-performance@postgresql.org
Cc: craig@postnewspapers.com.au, rjessil@yahoo.com
Subject: Re: slow delete
In-Reply-To: <486DB22F.10106@postnewspapers.com.au>
* 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