Re: Postgresql Materialized views

Dave Page wrote:
> On 12/01/2008, Mark Mielke <mark(at)mark(dot)mielke(dot)cc> wrote:
>
>> Jean-Michel Pouré wrote:
>>
>>> In my past development projects, I always used the concept of
>>> Materialized VIEW to speed-up SELECTs over INSERTs
>>>
>> Unless you are going to *pay* for it - you do realize that the best way
>> to get it implemented, would be to open up the source code, and give it
>> a try yourself?
>>
>
> In fairness to Jean-Michel, he has spent hundreds of hours in the past
> doing just that and far more for the pgAdmin users in the community -
> I'm sure we can excuse him for asking for what many do think would be
> a useful feature in the hopes that someone listening might just decide
> to pick it up.
>
> In the meantime though - have you tried rolling your own materialised
> views with some triggers Jean-Michel? I have good results doing that
> in the past
I'm not good with names - I suppose Jean-Michel should be asking his
benefactors to return the favour then? :-)

In my own case - I use a combination of triggers and application to
maintain materialized views - but the subject does seem complex to me.

The last two uses of materialized views I used:

Counts, because as we all know, PostgreSQL count(*) is slow, and in any
case, my count(*) is not on the whole table, but on a subset. Doing this
in a general way seems complex to me as it would need to be able to
evaluate whether a given INSERT or UPDATE or one of the dependent tables
would impact the WHERE clause for the materialized view, and it still
wouldn't know which rows to add/update/remove without detailed analysis,
so it would either be throwing out the entire materialized view and
recreating it on INSERT or UPDATE (or deferring until the next query?)
in which case it may be very slow, or it may be very complex.

Another one that I use is a complex join of several tables, and merging
1:N tables including aggregate queries into a 1:1 materialized view. I
see this as the same problem where it needs to do dependency analysis,
and it still doesn't know how to INSERT/UPDATE/DELETE materialized rows
without complex analysis forcing a re-build. In my case, it is 1 ms to
query my materialized view and 1500 ms to rebuild the materialized view.
I do NOT want to rebuild this view after every update.

In summary, I don't think materialized views is an easy thing to do.
Perhaps the very simplest of cases - but the simplest of cases can be
easily managed with triggers or application logic.

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>