Re: Postgresql Materialized views

Alvaro Herrera wrote: <blockquote cite="mid:20080114132644.GD4584@alvh.no-ip.org" type="cite"><pre wrap="">Mark Mielke
wrote:</pre><blockquote type="cite"><pre wrap="">FYI, my triggers are perhaps 10 lines each, and I believe I have
three
triggers in the 1500 ms -> 1 ms example. I have a view and a summary
table. I update the summary table from the view. In my opinion, this
solution is very manageable given the 1500:1 performance improvement
it grants me.   </pre></blockquote><pre wrap="">
But you had to modify your queries.  I would think that a materialized
views implementation worth its salt would put the view to work on the
original, unmodified queries. </pre></blockquote><br /> I might be slow today (everyday? :-) ) - but what do you mean
bythis? The only difference between *_table and *_view is that *_table is the summary table and *_view is the view. The
triggerson the tables the view is derived from select from *_view and update *_table. The queries remain unchanged
exceptfor deciding whether to use *_table or *_view. Yes, syntactical sugar would make it prettier and more manageable
-but I am confused as to how a syntactical sugar solution would improve performance, and I highly suspect it would not
achievethe same performance benefit. As I said - I can make assumptions about how the base tables are updated. A
generalizedsolution would not be able to make these assumptions?<br /><br /> For some further background - the base
tablesare a mirror of accpac tables (augh!) from mssql. The view and summary table gathers information from 5 or so of
thesetables including aggregates, conditionals, sub-selects (different queries to the same base tables) and deep joins.
Perhapsmy imagination is too limited - but I don't see how it would be easy to make syntactical sugar for this and
stillmaintain the performance I describe above. For about 30 lines of pl/pgsql and some application-side updates (again
fromthe view to the summary table) in the synchronization script it seems acceptable.<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>