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
