pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Craig Ringer <ringerc@ringerc.id.au>
To: Filippos Kalamidas <filippos.kal@gmail.com>
Cc: Sylvain CAILLET <scaillet@alaloop.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow Delete : Seq scan instead of index scan
Date: Wed, 17 Oct 2012 14:19:49 +0800
Message-ID: <507E4E05.9040400@ringerc.id.au> (raw)
In-Reply-To: <CANUP4k3ZUViuNwaMTs4kFUXvG6iAKofmGC==xNztduGXEjXRfQ@mail.gmail.com>
References: <507D1631.9050904@ringerc.id.au>
	<277219265.3802606.1350375882663.JavaMail.root@alaloop.com>
	<CANUP4k3ZUViuNwaMTs4kFUXvG6iAKofmGC==xNztduGXEjXRfQ@mail.gmail.com>

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

view thread (7+ messages)

Message-ID: <507E4E05.9040400@ringerc.id.au>
Permalink:  ../507E4E05.9040400@ringerc.id.au/
Also on:    postgresql.org/message-id/507E4E05.9040400@ringerc.id.au

copy link · copy postgr.es

reply

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: ringerc@ringerc.id.au, filippos.kal@gmail.com, scaillet@alaloop.com
  Subject: Re: Slow Delete : Seq scan instead of index scan
  In-Reply-To: <507E4E05.9040400@ringerc.id.au>

* 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