Обсуждение: OLAP and PG and deja-vu

Поиск
Список
Период
Сортировка

OLAP and PG and deja-vu

От
Mischa Sandberg
Дата:
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?