OLAP and PG and deja-vu - Mailing list pgsql-hackers

From Mischa Sandberg
Subject OLAP and PG and deja-vu
Msg-id 1114904486.427417a6a016f@webmail.telus.net
Whole thread Raw
List pgsql-hackers
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 
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 
Maybe one could kill a couple of birds with one stone, by making 
materialized views visible to the query optimizer? 

pgsql-hackers by date:

From: elein@varlena.com (elein)
Subject: Re: Output functions with multiple arguments considered harmful
From: Robert Treat
Subject: Re: [pgsql-advocacy] Increased company involvement