Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TO2H7-0003Rk-FE for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 08:13:17 +0000 Received: from mx0.network-studio.com ([62.93.225.99]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TO2H1-0002fs-Lf for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 08:13:17 +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 4E0BC4080E7; Tue, 16 Oct 2012 10:13:11 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by zimbra-ne01.network-studio.com (Postfix) with ESMTP id 48413DA127C; Tue, 16 Oct 2012 10:13:11 +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 cUFYfOOs4Gzk; Tue, 16 Oct 2012 10:13:10 +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 D56D7DA0FF8; Tue, 16 Oct 2012 10:13:10 +0200 (CEST) Date: Tue, 16 Oct 2012 10:13:10 +0200 (CEST) From: Sylvain CAILLET To: =?utf-8?Q?S=C3=A9kine?= Coulibaly Cc: pgsql-performance@postgresql.org Message-ID: <1295884445.3798248.1350375190799.JavaMail.root@alaloop.com> In-Reply-To: Subject: Re: Slow Delete : Seq scan instead of index scan MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3798247_841463420.1350375190798" 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/204 X-Sequence-Number: 48163 ------=_Part_3798247_841463420.1350375190798 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi S=C3=A9kine,=20 You're right : my question is why the planner doesn't use the index ! My DE= LETE statements have WHERE clause like : start_date<1346486100000. They are= executed to delete too old rows.=20 My postgresql version is 8.4. Below is an example of a table (they all have= the same structure) :=20 CREATE TABLE agg_t100_outgoing_a39_src_net_f5=20 (=20 total_pkts bigint,=20 end_date bigint,=20 src_network inet,=20 start_date bigint,=20 total_flows bigint,=20 total_bytes bigint=20 )=20 WITH (=20 OIDS=3DFALSE=20 );=20 CREATE INDEX agg_t100_outgoing_a39_src_net_f5_end_date=20 ON agg_t100_outgoing_a39_src_net_f5=20 USING btree=20 (end_date);=20 CREATE INDEX agg_t100_outgoing_a39_src_net_f5_start_date=20 ON agg_t100_outgoing_a39_src_net_f5=20 USING btree=20 (start_date);=20 I have investigated in the pg_stat_all_tables table and it seems the autova= ccum / autoanalyze don't do their job. Many tables have no last_autovacuum = / last_autoanalyze dates ! So the planner doesn't have fresh stats to decid= e. Don't you think it could be a good reason for slow DELETE ? In this case= , the trouble could come from the autovaccum configuration.=20 Regards,=20 Sylvain=20 ----- Mail original ----- > Hi Sylvain, > Might sound like a nasty question, and gurus will correct me if I'm > wrong, but first thing to investigate is why the index is not used : > - You have 2/3 million rows per table so the planner should use the > index. Seqscan is prefered for small tables. > - Maybe the WHERE clause of your DELETE statement doesn't make use of > your start and end date columns ? If so, in which order ? > Please, provide with your Pg version and the table setup with the > index. > Regards, > Sekine > 2012/10/16 Sylvain CAILLET < scaillet@alaloop.com > > > Hi to all, >=20 > > 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). >=20 > > 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). >=20 > > Do you have any idea about this trouble ? >=20 > > Sylvain Caillet >=20 > > Bureau : + 33 5 59 41 51 10 >=20 > > scaillet@alaloop.com >=20 > > ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart >=20 > > www.alaloop.com >=20 ------=_Part_3798247_841463420.1350375190798 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <= div style=3D'font-family: times new roman,new york,times,serif; font-size: = 10pt; color: #000000'>Hi S=C3=A9kine,
You're right : my question is w= hy the planner doesn't use the index ! My DELETE statements have WHERE clau= se like : start_date<1346486100000. They are executed to delete too old = rows.
My postgresql version is 8.= 4. Below is an example of a table (they all have the same structure) : = ;
CREATE TABLE agg_t100_outgoing_a39_src_net_f5
(
&nb= sp; total_pkts bigint,
  end_date bigint,
  src_n= etwork inet,
  start_date bigint,
  total_flows = bigint,
  total_bytes bigint
)
WITH (
=   OIDS=3DFALSE
);

CREATE INDEX agg_t100_o= utgoing_a39_src_net_f5_end_date
  ON agg_t100_outgoing_a39_src= _net_f5
  USING btree
  (end_date);
<= div>
<= /font>
CREATE INDEX agg_t100_outgoing_a39_src_net_f5_start_date
&n= bsp; ON agg_t100_outgoing_a39_src_net_f5
  USING btree<= /div>
  (start_date);

I have investigated in the&nbs= p;pg_stat_all_tables table and it seems the autovaccum / autoanalyze don't = do their job. Many tables have no last_autova= cuum / last_autoanalyze dates ! So the planner doesn't have fresh stats to = decide. Don't you think it could be a good reason for slow DELETE ? In this= case, the trouble could come from the autovaccum configuration.

Regards,

S= ylvain

Hi Sylvain,

Might sound like a nasty qu= estion, and gurus will correct me if I'm wrong, but first thing to investig= ate is why the index is not used :
- You have 2/3 million rows per table= so the planner should use the index. Seqscan is prefered for small tables.=
- Maybe the WHERE clause of your DELE= TE statement doesn't make use of your start and end date columns ? If so, i= n which order ?

Please, provide with your Pg version and the table setup with the index.

Regards,

Sekine

2012/10/16 Sylvain CAILLET <= scaillet@alaloop.com>
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 de= dicated server (Debian 6 - bi quad -&nb= sp;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 (bigi= nt). The Postgresql server has been tuned (I can give modified values if ne= eded).

I perform recurrent DELETE upon a table subset (~1900 t= ables) 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 d= isk for quick access).

Do you have any idea about this trouble ?

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

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



------=_Part_3798247_841463420.1350375190798--