Just finished writing the PG rules to maintain a bunch of materialized
(aggregate) views on a ROLAP cube --- yes, I've seen Jonathan
Gardner's "matview" for postgres; didnt cover what I needed :-(. PG
happens to be pretty convenient for roll-your-own OLAP, thanks to
RULES and ARRAY datatypes. So it hasn't been a pain to implement.
As it happens, what I've done amounts to implementing covering indexes
(that's another thread), and an aid for join-selectivity/result-size
estimation.
And suddenly I had a case of deja-vu...
A decade ago, working with Sybase 4.8/MSSQL 4.2, you could only
enforce FK relationships by coding them in triggers.
Then, one day, declarative-FK's could be recognized by the engine
itself. Suddenly, the query planner could make real use of those
relations, typically dropping them out of complex joins. Hurrah!
Now, here I am, doing what every ROLAP system needs: the ability to
store and query aggregates of the fact table, then parsing a query to
determine which (smallest) level of aggregate can answer the query.
There are many such app-level query generators that bear the brunt of
keeping track of, and choosing from, multiple aggregates --- a task
that's on a par with creating appropriate indexes, and about as much
fun.
Maybe one could kill a couple of birds with one stone, by making
materialized views visible to the query optimizer?
Discussion?