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@alaloop.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
