Re: Postgresql Materialized views

Dave Page wrote: <blockquote cite="mid:937d27e10801121029n10fd242gc5a7089e8905b0dc@mail.gmail.com" type="cite"><pre
wrap="">On12/01/2008, Mark Mielke <a class="moz-txt-link-rfc2396E"
href="mailto:mark@mark.mielke.cc"><mark@mark.mielke.cc></a>wrote: </pre><blockquote type="cite"><pre
wrap="">Jean-MichelPouré wrote:   </pre><blockquote type="cite"><pre wrap="">In my past development projects, I always
usedthe concept of
 
Materialized VIEW to speed-up SELECTs over INSERTs     </pre></blockquote><pre wrap="">Unless you are going to *pay*
forit - 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?   </pre></blockquote><pre wrap="">
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</pre></blockquote> I'm not good with names - I suppose Jean-Michel should be asking his benefactors to
returnthe favour then? :-)<br /><br /> In my own case - I use a combination of triggers and application to maintain
materializedviews - but the subject does seem complex to me.<br /><br /> The last two uses of materialized views I
used:<br/><br /> Counts, because as we all know, PostgreSQL count(*) is slow, and in any case, my count(*) is not on
thewhole table, but on a subset. Doing this in a general way seems complex to me as it would need to be able to
evaluatewhether a given INSERT or UPDATE or one of the dependent tables would impact the WHERE clause for the
materializedview, and it still wouldn't know which rows to add/update/remove without detailed analysis, so it would
eitherbe 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.<br /><br /> Another one that I use is a complex
joinof several tables, and merging 1:N tables including aggregate queries into a 1:1 materialized view. I see this as
thesame problem where it needs to do dependency analysis, and it still doesn't know how to INSERT/UPDATE/DELETE
materializedrows without complex analysis forcing a re-build. In my case, it is 1 ms to query my materialized view and
1500ms to rebuild the materialized view. I do NOT want to rebuild this view after every update.<br /><br /> In summary,
Idon't think materialized views is an easy thing to do. Perhaps the very simplest of cases - but the simplest of cases
canbe easily managed with triggers or application logic.<br /><br /> Cheers,<br /> mark<br /><br /><pre
class="moz-signature"cols="72">-- 
 
Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc"><mark@mielke.cc></a>
</pre>