Re: Postgresql Materialized views

> > 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.

Traditionally materialized views exist, so that you do not need to code differently.
Your queries still run on the detail table, but are silently answered
by a suitable MV. The MV might have count + other aggregated columns
grouped by some columns, and thus be able e.g. shortcircuit a
"select count(*) from atab". The MV should be MVCC aware (have different
values for different snapshots) and not substantially reduce possible
concurrency of updates to the base table.

> 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.

As long as you can formulate a normal view on the above statement,
you should be able to tell the db to materialize that.

A good MV feature would be able to use that MV regardless of whether
you select from the view, or use a statement that the view is a generalization of.

I think MV's where originally invented to boost benchmark results
and thus had to operate on given sql to base tables.

Andreas