Re: Performance on writable views

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)