Re: Slow Delete : Seq scan instead of index scan

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 <scaillet(at)alaloop(dot)com>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
>
>
>