Re: Slow Delete : Seq scan instead of index scan

Hi Sékine,

You're right : my question is why the planner doesn't use the index ! My DELETE statements have WHERE clause like : start_date<1346486100000. They are executed to delete too old rows.
My postgresql version is 8.4. Below is an example of a table (they all have the same structure) :

CREATE TABLE agg_t100_outgoing_a39_src_net_f5
(
total_pkts bigint,
end_date bigint,
src_network inet,
start_date bigint,
total_flows bigint,
total_bytes bigint
)
WITH (
OIDS=FALSE
);

CREATE INDEX agg_t100_outgoing_a39_src_net_f5_end_date
ON agg_t100_outgoing_a39_src_net_f5
USING btree
(end_date);

CREATE INDEX agg_t100_outgoing_a39_src_net_f5_start_date
ON agg_t100_outgoing_a39_src_net_f5
USING btree
(start_date);

I have investigated in the pg_stat_all_tables table and it seems the autovaccum / autoanalyze don't do their job. Many tables have no last_autovacuum / last_autoanalyze dates ! So the planner doesn't have fresh stats to decide. Don't you think it could be a good reason for slow DELETE ? In this case, the trouble could come from the autovaccum configuration.

Regards,

Sylvain
----- Mail original -----

> Hi Sylvain,

> Might sound like a nasty question, and gurus will correct me if I'm
> wrong, but first thing to investigate is why the index is not used :
> - You have 2/3 million rows per table so the planner should use the
> index. Seqscan is prefered for small tables.
> - Maybe the WHERE clause of your DELETE statement doesn't make use of
> your start and end date columns ? If so, in which order ?

> Please, provide with your Pg version and the table setup with the
> index.

> Regards,

> Sekine

> 2012/10/16 Sylvain CAILLET < scaillet(at)alaloop(dot)com >

> > 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. Autovaccum is on
> > and I expect the db stats to be updated in real time (pg_stats file
> > is stored in /dev/shm RAM disk for quick access).
>

> > Do you have any idea about this trouble ?
>

> > Sylvain Caillet
>
> > Bureau : + 33 5 59 41 51 10
>
> > scaillet(at)alaloop(dot)com
>

> > ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
>
> > www.alaloop.com
>