From: Sylvain CAILLET <scaillet@alaloop.com>
To: pgsql-performance@postgresql.org
Subject: Slow Delete : Seq scan instead of index scan
Date: Tue, 16 Oct 2012 09:50:12 +0200 (CEST)
Message-ID: <1837771448.3789694.1350373812378.JavaMail.root@alaloop.com> (raw)
In-Reply-To: <76919753.3785055.1350373170671.JavaMail.root@alaloop.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@alaloop.com
ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
www.alaloop.com
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
Subject: Re: Slow Delete : Seq scan instead of index scan
In-Reply-To: <1837771448.3789694.1350373812378.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