Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id DEBAA650E87
	for <pgsql-admin-postgresql.org@postgresql.org>;
	Thu,  3 Jul 2008 21:54:38 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 10327-02 for <pgsql-admin-postgresql.org@postgresql.org>;
	Thu,  3 Jul 2008 21:54:27 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from web56409.mail.re3.yahoo.com (web56409.mail.re3.yahoo.com
	[216.252.111.88])
	by postgresql.org (Postfix) with SMTP id 0D2A4650E85
	for <pgsql-admin@postgresql.org>; Thu,  3 Jul 2008 21:54:31 -0300 (ADT)
Received: (qmail 48478 invoked by uid 60001); 4 Jul 2008 00:47:49 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=XkfxdPuHfm08ouvEfNmyYVB6fDl0zUkcOAo8unDfNqfUO0LZpFfHsPghG/O2Ocgvw+RD6uXZJxEX1qUMIRegI1HlpFqY8SlhlDAmnIYCFkayTmIveJwnFidqjDH9ndy3oQc1Oi8tPLanH1DZxwsuzTGHcj0qwoR2JsjWg0TD/MA=;
Received: from [198.119.134.32] by web56409.mail.re3.yahoo.com via HTTP;
	Thu, 03 Jul 2008 17:47:49 PDT
X-Mailer: YahooMailRC/1042.33 YahooMailWebService/0.7.199
Date: Thu, 3 Jul 2008 17:47:49 -0700 (PDT)
From: Jessica Richard <rjessil@yahoo.com>
Subject: slow delete...
To: pgsql-admin@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1268677426-1215132469=:47881"
Message-ID: <695185.47881.qm@web56409.mail.re3.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200807/60
X-Sequence-Number: 29670

--0-1268677426-1215132469=:47881
Content-Type: text/plain; charset=us-ascii

 
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)


      
--0-1268677426-1215132469=:47881
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><div><script type="text/javascript" defer="defer">var YAHOO = {'Shortcuts' : {}};
YAHOO.Shortcuts.hasSensitiveText = false;
YAHOO.Shortcuts.sensitivityType = [];
YAHOO.Shortcuts.doUlt = false;
YAHOO.Shortcuts.location = "us";
YAHOO.Shortcuts.document_id = 0;
YAHOO.Shortcuts.document_type = "";
YAHOO.Shortcuts.document_title = "slow delete";
YAHOO.Shortcuts.document_publish_date = "";
YAHOO.Shortcuts.document_author = "rjessil@yahoo.com";
YAHOO.Shortcuts.document_url = "";
YAHOO.Shortcuts.document_tags = "";
YAHOO.Shortcuts.document_language = "english";
YAHOO.Shortcuts.annotationSet = {

};
</script>
<style type="text/css"><!--DIV {margin:0px;}--></style><link rel="stylesheet" href="http://d.yimg.com/jq/css/contextual_shortcuts.css" type="text/css" media="all"><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;"><div>I have a table with 29K rows total and I need to delete about 80K out of it.<br><br>I have a b-tree index on column cola (varchar(255) ) for my where clause to use.<br><br>my "select count(*) from test where cola = 'abc' runs very fast,<br>&nbsp;<br>but my actual "delete from test where cola = 'abc';" takes forever, never can finish and I haven't figured why....<br><br>In my explain output, what is that "Bitmap Heap Scan on table"? is it a table scan? is my index being used?<br><br>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?<br><br><br>by the way, there is a foreign key on another table that references the primary key col0 on table test.<br><br>Could some one help me out here?<br><br>Thanks a
 lot,<br>Jessica<br><br><br>testdb=# select count(*) from test;<br>&nbsp;count&nbsp; <br>--------<br>&nbsp;295793&nbsp; --total 295,793 rows<br>(1 row)<br><br>Time: 155.079 ms<br><br>testdb=# select count(*) from test where cola = 'abc';<br>&nbsp;count <br>-------<br>&nbsp;80998&nbsp; - need to delete 80,988 rows<br>(1 row)<br><br><br><br>testdb=# explain delete from test where cola = 'abc';<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 <br>----------------------------------------------------------------------------------------------------<br>&nbsp;Bitmap Heap Scan on test&nbsp; (cost=2110.49..10491.57 rows=79766 width=6)<br>&nbsp;&nbsp; Recheck Cond: ((cola)::text = 'abc'::text)<br>&nbsp;&nbsp; -&gt;&nbsp; Bitmap Index Scan on test_cola_idx&nbsp; (cost=0.00..2090.55 rows=79766 width=0)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: ((cola)::text = 'abc'::text)<br>(4 rows)<br></div></div><br>

      <span id="lw_beacon_1215132417677"></span><div style="top: -400px; left: -400px; position: absolute;" class="module overlay yui-module yui-overlay" id="lwPreview"><div class="hd"></div><div class="bd"></div><div class="ft"></div></div></div></div><br>



      </body></html>
--0-1268677426-1215132469=:47881--