From: John Papandriopoulos <dr.jpap@gmail.com>
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps
	vmem compared to SELECT
Date: Sun, 05 Dec 2010 03:14:46 -0800
Organization: Hub.Org Networking Services
Lines: 28
Message-ID: <idfs77$lsj$1@news.hub.org>
References: <idbo6g$92r$1@news.hub.org> <14012.1291443654@sss.pgh.pa.us>
	<idd14l$a4u$1@news.hub.org> <23792.1291480923@sss.pgh.pa.us>
	<idec54$2hpd$1@news.hub.org> <13159.1291502400@sss.pgh.pa.us>
	<4CFACC81.3050901@vmsinfo.com>
	<1628298022.20101205003839@erwied.eu>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.2.8) Gecko/20100802 Thunderbird/3.1.2
In-Reply-To: <1628298022.20101205003839@erwied.eu>
To: pgsql-performance@postgresql.org

On 12/4/10 3:38 PM, Jochen Erwied wrote:
> Sunday, December 5, 2010, 12:19:29 AM you wrote:
> 
>> Hmmm, what happens if I need 10 years of data, in monthly partitions? It
>> would be 120 partitions. Can you please elaborate on that limitation?
>> Any plans on lifting that restriction?
> 
> I'm running a partitioning scheme using 256 tables with a maximum of 16
> million rows (namely IPv4-addresses) and a current total of about 2.5
> billion rows, there are no deletes though, but lots of updates.
> 
> Using triggers or rules on the main table in my case showed to be not very
> effective, so I reverted to updating the inherited tables directly. This
> way you still can use a SELECT on the main table letting the optimizer do
> it's work, but do not run into the problem of oversized shared memory usage
> when doing DELETEs or UPDATEs
> 
> IMHO if you are using large partitioning schemes, handle the logic of which
> table to update or delete in your application. In most cases extending the
> underlying application will be much less work and more flexible than trying
> to write a dynamic rule/trigger to do the same job.
> 

Sounds like my experience exactly, however I am considering forgoing an update altogether, by just combining a DELETE with an INSERT.  I'm not sure how that might affect indexing performance as compared to an UPDATE.

I also had trouble with triggers; but found that if you use the "ONLY" keyword, they work again: see my original post of this thread.  In that case, the application SQL still retrains some simplicity.  On this topic, I think there's quite a bit of confusion and updates to the documentation would help greatly.

John