From: Sylvain CAILLET <scaillet@alaloop.com>
To: Craig Ringer <ringerc@ringerc.id.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow Delete : Seq scan instead of index scan
Date: Tue, 16 Oct 2012 10:24:42 +0200 (CEST)
Message-ID: <277219265.3802606.1350375882663.JavaMail.root@alaloop.com> (raw)
In-Reply-To: <507D1631.9050904@ringerc.id.au>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scaillet@alaloop.com, ringerc@ringerc.id.au
Subject: Re: Slow Delete : Seq scan instead of index scan
In-Reply-To: <277219265.3802606.1350375882663.JavaMail.root@alaloop.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox