Re: Postgresql Materialized views

Alvaro Herrera wrote:
> Mark Mielke wrote:
>
>> 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.
>>
>
> 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.
>

I might be slow today (everyday? :-) ) - but what do you mean by this?
The only difference between *_table and *_view is that *_table is the
summary table and *_view is the view. The triggers on the tables the
view is derived from select from *_view and update *_table. The queries
remain unchanged except for 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 achieve the same
performance benefit. As I said - I can make assumptions about how the
base tables are updated. A generalized solution would not be able to
make these assumptions?

For some further background - the base tables are a mirror of accpac
tables (augh!) from mssql. The view and summary table gathers
information from 5 or so of these tables including aggregates,
conditionals, sub-selects (different queries to the same base tables)
and deep joins. Perhaps my imagination is too limited - but I don't see
how it would be easy to make syntactical sugar for this and still
maintain the performance I describe above. For about 30 lines of
pl/pgsql and some application-side updates (again from the view to the
summary table) in the synchronization script it seems acceptable.

Cheers,
mark

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