pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedslow delete...
3+ messages / 2 participants
[nested] [flat]
* slow delete...
@ 2008-07-04 00:47 Jessica Richard <rjessil@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jessica Richard @ 2008-07-04 00:47 UTC (permalink / raw)
To: pgsql-admin
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: slow delete...
@ 2008-07-04 07:17 Charles Duffy <charles.duffy@gmail.com>
parent: Jessica Richard <rjessil@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Charles Duffy @ 2008-07-04 07:17 UTC (permalink / raw)
To: Jessica Richard <rjessil@yahoo.com>; +Cc: pgsql-admin
> by the way, there is a foreign key on another table that references the
> primary key col0 on table test.
>
Try putting an index on the FK column in the other table. Analyse the
table. Test by starting a transaction then
issuing: explain analyze delete from test where cola = 'abc'. Should be faster.
Charles Duffy
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: slow delete...
@ 2008-07-04 12:36 Jessica Richard <rjessil@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jessica Richard @ 2008-07-04 12:36 UTC (permalink / raw)
To: Charles Duffy <charles.duffy@gmail.com>; +Cc: pgsql-admin
thanks a lot. I imagine this will help me a lot. I will give it a try!!!
One more question: in the explain output, what is that "Bitmap Heap Scan on test"?, is it a bad thing (a table scan)? I would love to see the index scan instead of this Heap scan... what does "Heap Scan" actually do here?
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)
Thanks again,
Jessica
----- Original Message ----
From: Charles Duffy <charles.duffy@gmail.com>
To: Jessica Richard <rjessil@yahoo.com>
Cc: pgsql-admin@postgresql.org
Sent: Friday, July 4, 2008 3:17:43 AM
Subject: Re: [ADMIN] slow delete...
> by the way, there is a foreign key on another table that references the
> primary key col0 on table test.
>
Try putting an index on the FK column in the other table. Analyse the
table. Test by starting a transaction then
issuing: explain analyze delete from test where cola = 'abc'. Should be faster.
Charles Duffy
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-07-04 12:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-04 00:47 slow delete... Jessica Richard <rjessil@yahoo.com>
2008-07-04 07:17 ` Charles Duffy <charles.duffy@gmail.com>
2008-07-04 12:36 Re: slow delete... Jessica Richard <rjessil@yahoo.com>
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