Skip site navigation (1) Skip section navigation (2)

Re: Very long deletion time on a 200 GB database

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Reuven M(dot) Lerner" <reuven(at)lerner(dot)co(dot)il>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Very long deletion time on a 200 GB database
Date: 2012-02-23 19:04:34
Message-ID: 3806.1330023874@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
I just reread the original post and noted this:

"Reuven M. Lerner" <reuven(at)lerner(dot)co(dot)il> writes:
> (1) I tried to write this as a join, rather than a subselect.  But B has 
> an oid column that points to large objects, and on which we have a rule 
> that removes the associated large object when a row in B is removed. 

A rule?  Really?  That's probably bad enough in itself, but when you
write an overcomplicated join delete query, I bet the resulting plan
is spectacularly bad.  Have you looked at the EXPLAIN output for this?

I'd strongly recommend getting rid of the rule in favor of a trigger.
Also, as already noted, the extra join inside the IN sub-select is
probably hurting far more than it helps.

> (3) There are some foreign-key constraints on the B table.

If those are FK references *to* the B table, make sure the other end
(the referencing column) is indexed.  Postgres doesn't require an index
on a referencing column, but deletes in the referenced table will suck
if you haven't got one.

I don't think any of the fancy stuff being discussed in the thread is
worth worrying about until you've got these basic issues dealt with.

			regards, tom lane

In response to

pgsql-performance by date

Next:From: Andy ColsonDate: 2012-02-23 19:07:07
Subject: Re: set autovacuum=off
Previous:From: Peter van HardenbergDate: 2012-02-23 18:42:05
Subject: Re: set autovacuum=off

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group