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 del= ete statements. My db contains a little more than 10000 tables and runs on = a dedicated server (Debian 6 -=C2=A0bi quad = -=C2=A016Gb - SAS disks raid 0). Most= of the tables contains between 2 and 3 million rows and no foreign keys ex= ist between them. Each is indexed (btree) on start_date / end_date fields (= bigint). The Postgresql server has been tuned (I can give modified values i= f needed).

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= =C2=A0time ! 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 name=3D"x">
Sylvain Caillet
Bureau : + 33= 5 59 41 51 10
scaillet@alaloop.= com

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

--f46d0401fea5dccb8a04cc288f32-- From ringerc@ringerc.id.au Tue Oct 16 08:09:32 2012 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TO2DU-0001ck-Eu for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 08:09:32 +0000 Received: from mail-pa0-f46.google.com ([209.85.220.46]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TO2DP-0002bC-7c for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 08:09:32 +0000 Received: by mail-pa0-f46.google.com with SMTP id hz1so5675808pad.19 for ; Tue, 16 Oct 2012 01:09:26 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:content-transfer-encoding :x-gm-message-state; bh=SNwDWYrGj+VxJ2kUDA1aNErwtB1YRvUp6Kw8R+TzI/Y=; b=XrAYRjp9qnux024YOuQ2v/2CE2aO4SuQn1gpdqBirjIe4Hy2mWl93szDOHXg76Jxgg x5udpgpl78QUF8cJpDXCtrxp4FB9j2kjUAP5XhFTF4bp+lClkUSc4QClxZfspQ8PMAfX a15YewkInMLuLORUdbLcvmjzLPQDVCeXtQmHS16AviW8aQOky0mGKzaUnGxiX+SzSKTC RKaltERALHyHBW7tu1OrO1ctrW/5Q9/ajhjHHGht1qo6UgbuaP0SJ+iLrCt+J18ZBoWD mI1boBoT1t3KpKQAixjrQcHFHIF/S45FXcuOCoVDzODpSr9Cjk2oFWn2d/W8IAYPVRLl wCUA== Received: by 10.68.234.36 with SMTP id ub4mr44824703pbc.68.1350374966051; Tue, 16 Oct 2012 01:09:26 -0700 (PDT) Received: from ayaki.localdomain (office1.postnewspapers.com.au. [150.101.171.128]) by mx.google.com with ESMTPS id nm2sm2187568pbc.43.2012.10.16.01.09.23 (version=SSLv3 cipher=OTHER); Tue, 16 Oct 2012 01:09:25 -0700 (PDT) Message-ID: <507D1631.9050904@ringerc.id.au> Date: Tue, 16 Oct 2012 16:09:21 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:14.0) Gecko/20120717 Thunderbird/14.0 MIME-Version: 1.0 To: Sylvain CAILLET CC: pgsql-performance@postgresql.org Subject: Re: Slow Delete : Seq scan instead of index scan References: <1837771448.3789694.1350373812378.JavaMail.root@alaloop.com> In-Reply-To: <1837771448.3789694.1350373812378.JavaMail.root@alaloop.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Gm-Message-State: ALoCoQnoQKm9WatnTC4P+0u9u1DEKGkCnIxKvqrSncKqI5CdaK/BJD5x+YLGfAvqxyo3TAlordxn X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201210/203 X-Sequence-Number: 48162 On 10/16/2012 03:50 PM, Sylvain CAILLET wrote: > 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. Can you post that (or paste to explain.depesz.com and link to it here) along with a "\d tablename" from psql? -- Craig Ringer From scaillet@alaloop.com Tue Oct 16 08:13:17 2012 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-- From scaillet@alaloop.com Tue Oct 16 08:24:50 2012 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TO2SI-0007IC-1B for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 08:24:50 +0000 Received: from mx0.network-studio.com ([62.93.225.99]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TO2SC-0007rG-Rd for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 08:24:49 +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 31C1B4080E7; Tue, 16 Oct 2012 10:24:43 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by zimbra-ne01.network-studio.com (Postfix) with ESMTP id 2B3C1DA1E2C; Tue, 16 Oct 2012 10:24:43 +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 dy9GxigkSDi5; Tue, 16 Oct 2012 10:24:42 +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 BE4BFDA137C; Tue, 16 Oct 2012 10:24:42 +0200 (CEST) Date: Tue, 16 Oct 2012 10:24:42 +0200 (CEST) From: Sylvain CAILLET To: Craig Ringer Cc: pgsql-performance@postgresql.org Message-ID: <277219265.3802606.1350375882663.JavaMail.root@alaloop.com> In-Reply-To: <507D1631.9050904@ringerc.id.au> Subject: Re: Slow Delete : Seq scan instead of index scan MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3802605_1914825201.1350375882663" 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/205 X-Sequence-Number: 48164 ------=_Part_3802605_1914825201.1350375882663 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Hi Craig, Here are the outputs : flows=# explain analyze delete from agg_t377_incoming_a40_dst_net_f5 where start_date < 1346487911000; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on agg_t377_incoming_a40_dst_net_f5 (cost=0.00..34448.96 rows=657622 width=6) (actual time=3429.058..7135.901 rows=143 loops=1) Filter: (start_date < 1346487911000::bigint) Total runtime: 7136.191 ms (3 rows) flows=# \d agg_t377_incoming_a40_dst_net_f5 Table "public.agg_t377_incoming_a40_dst_net_f5" Column | Type | Modifiers -------------+--------+----------- end_date | bigint | dst_network | inet | total_pkts | bigint | total_bytes | bigint | start_date | bigint | total_flows | bigint | Indexes: "agg_t377_incoming_a40_dst_net_f5_end_date" btree (end_date) "agg_t377_incoming_a40_dst_net_f5_start_date" btree (start_date) Thanks for your help, Sylvain ----- Mail original ----- > On 10/16/2012 03:50 PM, Sylvain CAILLET wrote: > > 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. > Can you post that (or paste to explain.depesz.com and link to it > here) > along with a "\d tablename" from psql? > -- > Craig Ringer ------=_Part_3802605_1914825201.1350375882663 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 Craig,

Here are the outputs :

flows=3D# explain analyze delete from agg_t377_incoming_a40_dst_net_f5= where start_date < 1346487911000;

= = ; &nb= sp; QUERY PLAN &nbs= p; &n= bsp; =

-------------= ---------------------------------------------------------------------------= ---------------------------------------------------

Seq Scan on agg_t377_incoming_a40_dst_net_f5 = ;(cost=3D0.00..34448.96 rows=3D657622 width=3D6) (actual time=3D3429.058..7= 135.901 rows=3D143 loops=3D1)

&nb= sp; Filter: (start_date < 1346487911000::bigint)

Total runtime: 7136.191 ms

(3 rows)

flows=3D# \d agg_t377_incoming_a4= 0_dst_net_f5

Table "public.agg_t377_incoming_a40_dst_net_f5"=

Column | Type | Modifiers

-------------+--------+-----------

end_date &nb= sp;| bigint |

dst_network | inet | <= /div>

total_pkts | bigint |

total_bytes | bigint= |

start_date | bigint |

<= font face=3D"times new roman, new york, times, serif" size=3D"2"> tota= l_flows | bigint |

Indexes:

"agg_t377_i= ncoming_a40_dst_net_f5_end_date" btree (end_date)

"ag= g_t377_incoming_a40_dst_net_f5_start_date" btree (start_date)

<= div style=3D"color: rgb(0, 0, 0); font-family: 'times new roman', 'new york= ', times, serif; font-size: 10pt; ">

Thanks for your help,

Sylvain

On 10/16/2012 03:50 PM, Sylvain CAILLET = wrote:
> Hi to all,
>
> I've got a trouble with some dele= te statements. My db contains a little
> more than 10000 tables and r= uns on a dedicated server (Debian 6 - bi
> quad - 16Gb - SAS disks ra= id 0). Most of the tables contains between 2
> and 3 million rows and= no foreign keys exist between them. Each is
> indexed (btree) on sta= rt_date / end_date fields (bigint). The Postgresql
> server has been = tuned (I can give modified values if needed).
>
> I perform rec= urrent DELETE upon a table subset (~1900 tables) and each
> time, I d= elete a few lines (between 0 and 1200). Usually it takes
> between 10= s and more than 2mn. It seems to me to be a huge amount of
> t= ime ! An EXPLAIN ANALYZE on a DELETE shows me that the planner uses a
&g= t; Seq Scan instead of an Index Scan.

Can you post that (or paste to= explain.depesz.com and link to it here)
along with a "\d tablename" fr= om psql?

--
Craig Ringer

<= /html> ------=_Part_3802605_1914825201.1350375882663-- From filippos.kal@gmail.com Tue Oct 16 08:41:08 2012 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TO2i4-0000Qj-84 for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 08:41:08 +0000 Received: from mail-la0-f46.google.com ([209.85.215.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TO2hz-00087Z-L2 for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 08:41:07 +0000 Received: by mail-la0-f46.google.com with SMTP id h6so3974245lag.19 for ; Tue, 16 Oct 2012 01:41:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=8+JTd4IwCFIC30u+WsF0vfeyizJ0n4ONnJATd3ekcPI=; b=lF0oj3T3eED6YGcmoADrV3CXJwkzb5C0tbmOadvuyOfldnDyMazTyRM4wPLFZyQSxW 39BCpgD6mT10OoVCro25MBHBTrcGaLADfG/0WhqNi+1/DMzgJhxkGReqWEv1aPiDlWiK yRSbdCGBIFAV/s8OoJrfcZVq6Lp5Kws4/q6AwmQ2B/Ur2smoZxX/yLpv9xp2OIUHNCKX 0vUuBXyuGabja1Awc30oM0+o63DMog0JZrLQW533fHwdQfDpDWRt6HQHRb4Vq+JKl8nb CyI03ggdfqHkytpz3YHgfNdapa8fGd5/3Zrkg16Sffm0yYJgb6v7n2VEwxBVPiUImdIM aUAA== MIME-Version: 1.0 Received: by 10.112.101.41 with SMTP id fd9mr5205849lbb.132.1350376862125; Tue, 16 Oct 2012 01:41:02 -0700 (PDT) Received: by 10.114.66.74 with HTTP; Tue, 16 Oct 2012 01:41:02 -0700 (PDT) In-Reply-To: <277219265.3802606.1350375882663.JavaMail.root@alaloop.com> References: <507D1631.9050904@ringerc.id.au> <277219265.3802606.1350375882663.JavaMail.root@alaloop.com> Date: Tue, 16 Oct 2012 11:41:02 +0300 Message-ID: Subject: Re: Slow Delete : Seq scan instead of index scan From: Filippos Kalamidas To: Sylvain CAILLET Cc: Craig Ringer , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=14dae9d7180c2e0ebb04cc291d84 X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201210/206 X-Sequence-Number: 48165 --14dae9d7180c2e0ebb04cc291d84 Content-Type: text/plain; charset=ISO-8859-1 the first thing you should probably do is run an 'analyze' on one of these tables and then run again the delete statement. if there are no stats for these tables, it's normal not to have very good plans. On Tue, Oct 16, 2012 at 11:24 AM, Sylvain CAILLET wrote: > Hi Craig, > > Here are the outputs : > > flows=# explain analyze delete from agg_t377_incoming_a40_dst_net_f5 where > start_date < 1346487911000; > QUERY PLAN > > > ------------------------------------------------------------------------------------------------------------------------------------------- > Seq Scan on agg_t377_incoming_a40_dst_net_f5 (cost=0.00..34448.96 > rows=657622 width=6) (actual time=3429.058..7135.901 rows=143 loops=1) > Filter: (start_date < 1346487911000::bigint) > Total runtime: 7136.191 ms > (3 rows) > flows=# \d agg_t377_incoming_a40_dst_net_f5 > Table "public.agg_t377_incoming_a40_dst_net_f5" > Column | Type | Modifiers > -------------+--------+----------- > end_date | bigint | > dst_network | inet | > total_pkts | bigint | > total_bytes | bigint | > start_date | bigint | > total_flows | bigint | > Indexes: > "agg_t377_incoming_a40_dst_net_f5_end_date" btree (end_date) > "agg_t377_incoming_a40_dst_net_f5_start_date" btree (start_date) > > Thanks for your help, > > Sylvain > > ------------------------------ > > On 10/16/2012 03:50 PM, Sylvain CAILLET wrote: > > 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. > > Can you post that (or paste to explain.depesz.com and link to it here) > along with a "\d tablename" from psql? > > -- > Craig Ringer > > > --14dae9d7180c2e0ebb04cc291d84 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable the first thing you should probably do is run an 'analyze' on one o= f these tables and then run again the delete statement. if there are no sta= ts for these tables, it's normal not to have very good plans.

On Tue, Oct 16, 2012 at 11:24 AM, Sylvai= n CAILLET <scaillet@alaloop.com> wrote:

Hi Craig,

Here are the outputs :

flows=3D# explain analyze delete from agg= _t377_incoming_a40_dst_net_f5 where start_date < 1346487911000;
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 QUERY PLAN =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0
---------------------------------------------------------------------------= ----------------------------------------------------------------
=A0Seq Scan on agg_t377_incoming_a40_dst_net_f5 =A0(cost=3D0.00..34448.96 r= ows=3D657622 width=3D6) (actual time=3D3429.058..7135.901 rows=3D143 loops= =3D1)
=A0 =A0Filter: (start_date < 1346487911000::bigint)
=A0Total runtime: 7136.191 ms
(3 rows)
flows=3D# \d agg_t377_incoming_a40_dst_net_f5
Table "public.agg_t377_= incoming_a40_dst_net_f5"

=A0 =A0Column = =A0 =A0| =A0Type =A0| Modifiers=A0
-------------+--------+-----------
=A0end_date= =A0 =A0| bigint |=A0

=A0dst_network = | inet =A0 |=A0
=A0total_pkts =A0| bigint |=A0
=A0total_bytes | bigint |=A0

=A0start_date = =A0| bigint |=A0
=A0total_flows | bigint |=A0
Indexes:

=A0 =A0 "a= gg_t377_incoming_a40_dst_net_f5_end_date" btree (end_date)
=A0 =A0 "= agg_t377_incoming_a40_dst_net_f5_start_date" btree (start_date)=

Thanks for = your help,

Sylvain

On 10/16/2012 03:50 PM, Sylvain CAILLET wrote:
> 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 (Debi= an 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 Post= gresql
> server has been tuned (I can give modified values if needed).
><= br>> I perform recurrent DELETE upon a table subset (~1900 tables) and e= ach
> time, I delete a few lines (between 0 and 1200). Usually it tak= es
> between 10s and more than 2mn. It seems to me to be a huge amount of> =A0 time ! An EXPLAIN ANALYZE on a DELETE shows me that the planner = uses a
> Seq Scan instead of an Index Scan.

Can you post that = (or paste to explai= n.depesz.com and link to it here)
along with a "\d tablename" from psql?

--
Craig Ringer<= br>

--14dae9d7180c2e0ebb04cc291d84-- From ringerc@ringerc.id.au Wed Oct 17 06:20:04 2012 Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TOMz6-0007Mi-2p for pgsql-performance@postgresql.org; Wed, 17 Oct 2012 06:20:04 +0000 Received: from mail-pa0-f46.google.com ([209.85.220.46]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TOMz0-0005V2-CT for pgsql-performance@postgresql.org; Wed, 17 Oct 2012 06:20:03 +0000 Received: by mail-pa0-f46.google.com with SMTP id hz1so6732847pad.19 for ; Tue, 16 Oct 2012 23:19:55 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:content-transfer-encoding :x-gm-message-state; bh=thnnm5u2Y1xlsUGMiqY9II1Dy6Es4eiKh2pW9h2Ok08=; b=GoVuMcCkIoPY61MMDR/rcQ0nQH8hIfWFkIh0WEtSTzoCLTk6Fo3iLTY/j3yY5KPN7Y Oiau/9mYTnXq08Q+bdp+m04lSCH55aJjRHGq/49xUXuQ1CQfMZey8wT4ykwy+jU4WlxQ C2nBkH5AzLpwqd6rfBWaa3mNp3ZPA0Y0+f8HPwGqb+0dlypVYn+e8KjvK9PZVah2vbRz D32UO4JkNAUVG35OBA5WBDwOxI1CKroSJDGWUM1vo92ybes9u6XlvJZ/53uBYtiFjWZI ScdeH60ZWM6/E86eKkLFq5x91oT1/F8iuxkPdEzXwDEC96O0iHUU6F1IN2Zv15oG9Nr5 mHeA== Received: by 10.68.213.199 with SMTP id nu7mr54101601pbc.75.1350454795553; Tue, 16 Oct 2012 23:19:55 -0700 (PDT) Received: from ayaki.localdomain (office1.postnewspapers.com.au. [150.101.171.128]) by mx.google.com with ESMTPS id pj10sm11992115pbb.46.2012.10.16.23.19.51 (version=SSLv3 cipher=OTHER); Tue, 16 Oct 2012 23:19:54 -0700 (PDT) Message-ID: <507E4E05.9040400@ringerc.id.au> Date: Wed, 17 Oct 2012 14:19:49 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:14.0) Gecko/20120717 Thunderbird/14.0 MIME-Version: 1.0 To: Filippos Kalamidas CC: Sylvain CAILLET , pgsql-performance@postgresql.org Subject: Re: Slow Delete : Seq scan instead of index scan References: <507D1631.9050904@ringerc.id.au> <277219265.3802606.1350375882663.JavaMail.root@alaloop.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Gm-Message-State: ALoCoQkC8qqaTZEw4WVKRNZjeKzcl4/jhTjfBh4s12+RrD9dPyceCSR93NmY0mOfr8TlUGv8HRN2 X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201210/225 X-Sequence-Number: 48184 On 10/16/2012 04:41 PM, Filippos Kalamidas wrote: > the first thing you should probably do is run an 'analyze' on one of > these tables and then run again the delete statement. if there are no > stats for these tables, it's normal not to have very good plans. Yep, and the fact that the stats are that bad suggests that autovaccum probably isn't running, or isn't running often enough. If you have a high INSERT/UPDATE/DELETE load, then turn autovacuum up on that table. See: http://www.postgresql.org/docs/current/static/routine-vacuuming.html http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html If the table is badly bloated it might be worth running "VACUUM FULL" on it or (if you're on PostgreSQL 8.4 or below) instead CLUSTER the table on an index, as "VACUUM FULL" is very inefficient in 8.4 and older (I think; I might be misremembering the versions). Please specify your PostgreSQL version in all questions. See https://wiki.postgresql.org/wiki/Slow_Query_Questions -- Craig Ringer