pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: adey <adey11@gmail.com>
Cc: Pgsql-Admin <pgsql-admin@postgresql.org>
Subject: Re: Delete performance
Date: Tue, 21 Feb 2006 10:01:27 -0500
Message-ID: <15417.1140534087@sss.pgh.pa.us> (raw)
In-Reply-To: <1c66bda80602202302u2f6f6bc0jcfc3c728940ad2d0@mail.gmail.com>
References: <1c66bda80602202302u2f6f6bc0jcfc3c728940ad2d0@mail.gmail.com>

adey <adey11@gmail.com> writes:
> We are attempting many deletes in our production database for the first
> time, and we're getting nowhere fast.
> The SQL runs for more than 12 hours to delete 2 million rows, and hasn't
> finished each time we've tried it as we've had to cancel it.

The usual cause of slow deletes is that (a) the table is the target of
some foreign key references from other large tables, and (b) the
referencing columns in those tables aren't indexed, or (in older PG
versions such as 7.4) aren't exactly the same datatype as the master
column.  This forces the FK actions to use inefficient sequential-scan
plans.  Fix the index situation and then start a fresh session to ensure
you have fresh FK-action plans.

Please also think *hard* about running something more modern than 7.4.2.
That release series is at 7.4.12 --- you are missing nearly two years'
worth of critical bug fixes.

			regards, tom lane



view thread (7+ messages)  latest in thread

Message-ID: <15417.1140534087@sss.pgh.pa.us>
Permalink:  ../15417.1140534087@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/15417.1140534087@sss.pgh.pa.us

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, adey11@gmail.com
  Subject: Re: Delete performance
  In-Reply-To: <15417.1140534087@sss.pgh.pa.us>
  References: <1c66bda80602202302u2f6f6bc0jcfc3c728940ad2d0@mail.gmail.com> <15417.1140534087@sss.pgh.pa.us>

* 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