> > 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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly