Re: Slow Delete : Seq scan instead of index scan

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
>
>