Re: Slow Delete : Seq scan instead of index scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux