X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 35B4AD1D132 for ; Tue, 24 Feb 2004 20:19:07 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 68806-10 for ; Tue, 24 Feb 2004 16:19:07 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 8C9FDD1CAF6 for ; Tue, 24 Feb 2004 16:19:05 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i1OKJ51Y048492 for ; Tue, 24 Feb 2004 20:19:05 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i1OJohoG042815 for pgsql-performance@postgresql.org; Tue, 24 Feb 2004 19:50:43 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: [PERFORMANCE] slow small delete on large table Date: Tue, 24 Feb 2004 14:12:54 -0500 Organization: cbbrowne Computing Inc Lines: 33 Message-ID: References: <200402231910.57078.pgsql@bluepolka.net> <18315.1077600238@sss.pgh.pa.us> <200402241136.08556.pgsql@bluepolka.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Security Through Obscurity, linux) Cancel-Lock: sha1:bJlAOoKOOrV/Zx3mv4d+tebP36E= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/287 X-Sequence-Number: 5811 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." 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