From: Tom Lane <tgl@sss.pgh.pa.us>
To: Reuven M. Lerner <reuven@lerner.co.il>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very long deletion time on a 200 GB database
Date: Thu, 23 Feb 2012 14:04:34 -0500
Message-ID: <3806.1330023874@sss.pgh.pa.us> (raw)
In-Reply-To: <4F45FB55.1020600@lerner.co.il>
References: <4F45FB55.1020600@lerner.co.il>
I just reread the original post and noted this:
"Reuven M. Lerner" <reuven@lerner.co.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
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: tgl@sss.pgh.pa.us, reuven@lerner.co.il
Subject: Re: Very long deletion time on a 200 GB database
In-Reply-To: <3806.1330023874@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