469,938 Members | 2,496 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,938 developers. It's quick & easy.

Very slow delete.

Hello,

I have a table with 29268 odd records. Deleting records is VERY slow,
and I don't know why.

I explained analysed the following query:
delete from people where id < '2000'

Index Scan using people_pkey on people (cost=0.00..71.68 rows=2792
width=6) (actual time=1.361..5.657 rows=2000 loops=1)
Index Cond: (id < 3000)
Total runtime: 13.006 ms
3 row(s)
Total runtime: 63,869.322 ms

using phppgadmin (it's also slow via command line or Perl/DBD).

I haven't optimized postgresql yet (kernel.shmmax, etc), but I don't
see why it is taking this long. Why is it 13ms, and then 63869ms?

There is 1 fkey relationship, and it is primary key to a few other
(now empty) tables.

The fkey's are on update cascade on delete restrict. Deletes on other
tables are quick.

Any ideas?

Thanks

Brock

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
2 9593
Brock Henry <br*********@gmail.com> writes:
delete from people where id < '2000' Index Scan using people_pkey on people (cost=0.00..71.68 rows=2792
width=6) (actual time=1.361..5.657 rows=2000 loops=1)
Index Cond: (id < 3000)
Total runtime: 13.006 ms
3 row(s)
Total runtime: 63,869.322 ms


So 13 msec to find the rows to delete, and 63850+ msec in overhead.
Which is certainly from the foreign keys that reference this table,
because the referencing tables have to be checked to see if they
contain copies of the key values being deleted.

You either don't have indexes on the referencing columns, or there
is a datatype mismatch, or possibly you need to update statistics
for those tables.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2
Hi Tom,

Thanks for your help. I checked types and indexes, to no avail. Vacuum
didn't help. but vacuum full did, it's now fast again.

Cheers,

Brock

On Mon, 11 Oct 2004 23:38:49 -0400, Tom Lane <tg*@sss.pgh.pa.us> wrote:
Brock Henry <br*********@gmail.com> writes:
delete from people where id < '2000'

Index Scan using people_pkey on people (cost=0.00..71.68 rows=2792
width=6) (actual time=1.361..5.657 rows=2000 loops=1)
Index Cond: (id < 3000)
Total runtime: 13.006 ms
3 row(s)
Total runtime: 63,869.322 ms


So 13 msec to find the rows to delete, and 63850+ msec in overhead.
Which is certainly from the foreign keys that reference this table,
because the referencing tables have to be checked to see if they
contain copies of the key values being deleted.

You either don't have indexes on the referencing columns, or there
is a datatype mismatch, or possibly you need to update statistics
for those tables.

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Jan Suchanek | last post: by
6 posts views Thread by Rory Campbell-Lange | last post: by
50 posts views Thread by diffuser78 | last post: by
1 post views Thread by sunith | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.