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--