Message-ID: <507D1631.9050904@ringerc.id.au>
Date: Tue, 16 Oct 2012 16:09:21 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: Sylvain CAILLET <scaillet@alaloop.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Slow Delete : Seq scan instead of index scan
References: <1837771448.3789694.1350373812378.JavaMail.root@alaloop.com>
In-Reply-To: <1837771448.3789694.1350373812378.JavaMail.root@alaloop.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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