From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORMANCE] slow small delete on large table
Date: Tue, 24 Feb 2004 14:12:54 -0500
Message-ID: <m3r7wk5m15.fsf@wolfe.cbbrowne.com> (raw)
References: <200402231910.57078.pgsql@bluepolka.net>
<18315.1077600238@sss.pgh.pa.us>
<200402241136.08556.pgsql@bluepolka.net>
After a long battle with technology, pgsql@bluepolka.net ("Ed L."), an earthling, wrote:
> On Monday February 23 2004 10:23, Tom Lane wrote:
>> "Ed L." <pgsql@bluepolka.net> writes:
>> Depending on the size of mytable, you might need an "ANALYZE doomed"
>> in there, but I'm suspecting not. A quick experiment suggests that
>> you'll get a plan with an inner indexscan on mytable.key, which is
>> exactly what you need.
>
> I didn't mention I'd written a trigger to do delete N rows on each new
> insert (with a delay governor preventing deletion avalanches). The
> approach looks a little heavy to be done from within a trigger with the
> response time I need, but I'll try it. Cantchajust toss in that "limit N"
> functionality to delete clauses? How hard could that be? ;)
It's nonstandard, which will get you a certain amount of opposition
"for free;" the problem with nonstandard behaviour is that sometimes
the implications haven't been thought out...
>> See also Chris Browne's excellent suggestions nearby, if you are willing
>> to make larger readjustments in your thinking...
>
> I did a search for articles by Chris Browne, didn't see one that
> appeared relevant. What is the thread subject to which you refer?
It's in the same thread. I suggested having a daemon running a cursor
(amounting to a slightly more expensive version of Tom's "doomed temp
table" approach), or using "rotor" tables where you could TRUNCATE a
table every few days which would be _really_ cheap...
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://cbbrowne.com/info/emacs.html
Expect the unexpected.
-- The Hitchhiker's Guide to the Galaxy, page 7023
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: cbbrowne@acm.org
Subject: Re: [PERFORMANCE] slow small delete on large table
In-Reply-To: <m3r7wk5m15.fsf@wolfe.cbbrowne.com>
* 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