Received: from magus.postgresql.org ([87.238.57.229])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <scaillet@alaloop.com>) id 1TO1ut-000059-RA
	for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 07:50:19 +0000
Received: from mx0.network-studio.com ([62.93.225.99])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <scaillet@alaloop.com>) id 1TO1uo-0002Ma-EA
	for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 07:50:19 +0000
Received: from zimbra-ne01.network-studio.com (zimbra-admin.network-studio.com
	[192.168.101.80])
	by mx0.network-studio.com (Postfix) with ESMTP id ACA774080A7
	for <pgsql-performance@postgresql.org>;
	Tue, 16 Oct 2012 09:50:12 +0200 (CEST)
Received: from localhost (localhost [127.0.0.1])
	by zimbra-ne01.network-studio.com (Postfix) with ESMTP id A8E3EDA149C
	for <pgsql-performance@postgresql.org>;
	Tue, 16 Oct 2012 09:50:12 +0200 (CEST)
X-Virus-Scanned: amavisd-new at zimbra-ne01.network-studio.com
Received: from zimbra-ne01.network-studio.com ([127.0.0.1])
	by localhost (zimbra-ne01.network-studio.com [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id wjleLu7cpIgH for <pgsql-performance@postgresql.org>;
	Tue, 16 Oct 2012 09:50:12 +0200 (CEST)
Received: from zimbra-ne01.network-studio.com (zimbra-ne01.network-studio.com
	[192.168.101.80])
	by zimbra-ne01.network-studio.com (Postfix) with ESMTP id 6E77EDA0570
	for <pgsql-performance@postgresql.org>;
	Tue, 16 Oct 2012 09:50:12 +0200 (CEST)
Date: Tue, 16 Oct 2012 09:50:12 +0200 (CEST)
From: Sylvain CAILLET <scaillet@alaloop.com>
To: pgsql-performance@postgresql.org
Message-ID: <1837771448.3789694.1350373812378.JavaMail.root@alaloop.com>
In-Reply-To: <76919753.3785055.1350373170671.JavaMail.root@alaloop.com>
Subject: Slow Delete : Seq scan instead of index scan
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_3789693_1370987673.1350373812377"
X-Originating-IP: [192.168.101.82]
X-Mailer: Zimbra 7.2.1_GA_2790 (ZimbraWebClient - GC22 (Linux)/7.2.1_GA_2790)
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201210/201
X-Sequence-Number: 48160

------=_Part_3789693_1370987673.1350373812377
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Hi to all, 


I've got a trouble with some delete statements. My db contains a little more than 10000 tables and runs on a dedicated server (Debian 6 - bi quad - 16Gb - SAS disks raid 0). Most of the tables contains between 2 and 3 million rows and no foreign keys exist between them. Each is indexed (btree) on start_date / end_date fields (bigint). The Postgresql server has been tuned (I can give modified values if needed). 


I perform recurrent DELETE upon a table subset (~1900 tables) and each time, I delete a few lines (between 0 and 1200). Usually it takes between 10s and more than 2mn. It seems to me to be a huge amount of time ! An EXPLAIN ANALYZE on a DELETE shows me that the planner uses a Seq Scan instead of an Index Scan. Autovaccum is on and I expect the db stats to be updated in real time (pg_stats file is stored in /dev/shm RAM disk for quick access). 


Do you have any idea about this trouble ? 



Sylvain Caillet 
Bureau : + 33 5 59 41 51 10 
scaillet@alaloop.com 

ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart 
www.alaloop.com 

------=_Part_3789693_1370987673.1350373812377
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><style type=3D'text/css'>p { margin: 0; }</style></head><body><=
div style=3D'font-family: times new roman,new york,times,serif; font-size: =
10pt; color: #000000'>Hi to all,<div><br></div><div>I've got a trouble with=
 some delete statements. My db contains a little more than 10000 tables and=
 runs on a dedicated server (Debian 6 -&nbsp;<span style=3D"font-size: 10pt=
; ">bi quad -&nbsp;</span><span style=3D"font-size: 10pt; ">16Gb - SAS disk=
s raid 0). Most of the tables contains between 2 and 3 million rows and no =
foreign keys exist between them. Each is indexed (btree) on start_date / en=
d_date fields (bigint). The Postgresql server has been tuned (I can give mo=
dified values if needed).</span></div><div><br></div><div>I perform recurre=
nt DELETE upon a table subset (~1900 tables) and each time, I delete a few =
lines (between 0 and 1200). Usually it takes between 10s and more than 2mn.=
 It seems to me to be a huge amount of &nbsp;time ! An EXPLAIN ANALYZE on a=
 DELETE shows me that the planner uses a Seq Scan instead of an Index Scan.=
 Autovaccum is on and I expect the db stats to be updated in real time (pg_=
stats file is stored in /dev/shm RAM disk for quick access).</div><div><br>=
</div><div>Do you have any idea about this trouble ?<br><br><div><span name=
=3D"x"></span><div>Sylvain Caillet<br>Bureau : + 33 5 59 41 51 10<br><a hre=
f=3D"http://scaillet@alaloop.com">scaillet@alaloop.com</a><br><br>ALALOOP S=
.A.S. - Technopole Izarbel - 64210 Bidart<br><a href=3D"http://www.alaloop.=
com">www.alaloop.com</a> </div><span name=3D"x"></span><br></div></div></di=
v></body></html>
------=_Part_3789693_1370987673.1350373812377--