Message-ID: <507E4E05.9040400@ringerc.id.au>
Date: Wed, 17 Oct 2012 14:19:49 +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: Filippos Kalamidas <filippos.kal@gmail.com>
CC: Sylvain CAILLET <scaillet@alaloop.com>, pgsql-performance@postgresql.org
Subject: Re: Slow Delete : Seq scan instead of index scan
References: <507D1631.9050904@ringerc.id.au>
	<277219265.3802606.1350375882663.JavaMail.root@alaloop.com>
	<CANUP4k3ZUViuNwaMTs4kFUXvG6iAKofmGC==xNztduGXEjXRfQ@mail.gmail.com>
In-Reply-To: 
 <CANUP4k3ZUViuNwaMTs4kFUXvG6iAKofmGC==xNztduGXEjXRfQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/16/2012 04:41 PM, Filippos Kalamidas wrote:
> the first thing you should probably do is run an 'analyze' on one of
> these tables and then run again the delete statement. if there are no
> stats for these tables, it's normal not to have very good plans.

Yep, and the fact that the stats are that bad suggests that autovaccum 
probably isn't running, or isn't running often enough.

If you have a high INSERT/UPDATE/DELETE load, then turn autovacuum up on 
that table. See:

   http://www.postgresql.org/docs/current/static/routine-vacuuming.html

 
http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html


If the table is badly bloated it might be worth running "VACUUM FULL" on 
it or (if you're on PostgreSQL 8.4 or below) instead CLUSTER the table 
on an index, as "VACUUM FULL" is very inefficient in 8.4 and older (I 
think; I might be misremembering the versions).


Please specify your PostgreSQL version in all questions. See 
https://wiki.postgresql.org/wiki/Slow_Query_Questions

--
Craig Ringer