slow delete

From: Jessica Richard <rjessil(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: slow delete
Date: 2008-07-04 00:44:40
Message-ID: 357892.18257.qm@web56412.mail.re3.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have a table with 29K rows total and I need to delete about 80K out of it.

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....

In my explain output, what is that "Bitmap Heap Scan on table"? is it a table scan? is my index being used?

How does delete work? to delete 80K rows that meet my condition, does Postgres find them all and delete them all together or one at a time?

by the way, there is a foreign key on another table that references the primary key col0 on table test.

Could some one help me out here?

Thanks a lot,
Jessica

testdb=# select count(*) from test;
count
--------
295793 --total 295,793 rows
(1 row)

Time: 155.079 ms

testdb=# select count(*) from test where cola = 'abc';
count
-------
80998 - need to delete 80,988 rows
(1 row)

testdb=# explain delete from test where cola = 'abc';
QUERY PLAN
----------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=2110.49..10491.57 rows=79766 width=6)
Recheck Cond: ((cola)::text = 'abc'::text)
-> Bitmap Index Scan on test_cola_idx (cost=0.00..2090.55 rows=79766 width=0)
Index Cond: ((cola)::text = 'abc'::text)
(4 rows)

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message idc danny 2008-07-04 04:52:03 Define all IP's in the world in pg_hba.conf
Previous Message PFC 2008-07-03 12:56:27 Re: [QUESTION]Concurrent Access