In-Reply-To: <46BDC091.4090109@commandprompt.com>
References: <20070808124054.GC27936@nibiru.local>
	<46BD687E.1040100@enterprisedb.com>
	<46BDC091.4090109@commandprompt.com>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <6F774767-8871-45DC-AE31-FD3D7A5A2F46@decibel.org>
Cc: Heikki Linnakangas <heikki@enterprisedb.com>,
	pgsql-sql <pgsql-sql@postgresql.org>,
	postgresql performance list <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Jim Nasby <decibel@decibel.org>
Subject: Re: Performance on writable views
Date: Mon, 13 Aug 2007 17:17:16 -0500
To: Joshua D. Drake <jd@commandprompt.com>

On Aug 11, 2007, at 8:58 AM, Joshua D. Drake wrote:
> Heikki Linnakangas wrote:
>> Enrico Weigelt wrote:
>>> I'm often using writable views as interfaces to clients, so
>>> they only see "virtual" objects and never have to cope with
>>> the actual storage, ie. to give some client an totally
>>> denormalized view of certain things, containing only those
>>> information required for certain kind of operations.
>
>>> Now I've got the strange feeling that this makes updates
>>> slow, since it always has to run the whole view query to
>>> fetch an record to be updated (ie. to get OLD.*).
>>
>> There is some overhead in rewriting the query, but it shouldn't be
>> significantly slower than issuing the statements behind the view
>> directly. I wouldn't worry about it, unless you have concrete  
>> evidence
>> that it's causing problems.
>
> I don't know about that, at least when using rules for partitioning  
> the
> impact can be significant in comparison to triggers.

That's because you have to re-evaluate the input query for each rule  
that's defined, so even if you only have rules for 2 partitions in a  
table (which is really about the minimum you can have, at least for  
some period of overlap surrounding the time when you switch to a new  
partition), you're looking at evaluating every input query twice.

In this case, the rules presumably are just simply re-directing DML,  
so there'd only be one rule in play at a time. That means the only  
real overhead is in the rewrite engine.
-- 
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)