Skip site navigation (1) Skip section navigation (2)

OLAP versus Materialized Views?

From: Jonathan Gardner <jgardner(at)jonathangardner(dot)net>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: OLAP versus Materialized Views?
Date: 2004-04-27 22:47:33
Message-ID: 200404271547.33627.jgardner@jonathangardner.net (view raw or flat)
Thread:
Lists: pgsql-hackers
I've just discovered OLAP and it looks like a competing technology with 
materialized views.

In a nutshell, OLAP seems to be "pre-storing" the results of potential 
queries. When queries are made with those conditions, then the pre-stored 
results are used. It seems most common for join conditions or aggregates 
with conditions. (Corrections welcome...)

This seems like a generally useful tool. I expect PostgreSQL should one day 
have a feature like this.

OLAP usage would basically be describing how the data fits together and 
predicting what kinds of queries will be made. Then, internally the 
implementation would decide what data should be cached. Perhaps it can do 
some analysis of the data and the queries being run to come up with a fair 
compromise.

Materialized Views are a simpler concept, but of limited usefulness. Having 
a bunch of materialized views that are very similar would be less efficient 
overall than having OLAP configured properly.

Questions:
(1) Has anyone been doing any work or serious thought on anything like this?

(2) Should I be focusing on OLAP rather than materialized views? With 
materialized views, a few specific queries work really well, but related 
queries get no benefit. Apparently, with OLAP, a whole class of queries 
will get a huge boost. I'm not certain that OLAP and materialized views are 
entirely different.

(3) I can't seem to find the paper on the twelve laws of OLAP, but it gets a 
lot of mentioning. Does anyone know where I can obtain it?

Future Thoughts:
OLAP relies on knowing how the data fits together and what kinds of queries 
are being done. It could be possible, just by analyzing the queries being 
run, to have OLAP automatically kick in without user intervention.

-- 
Jonathan Gardner
jgardner(at)jonathangardner(dot)net

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2004-04-27 23:12:57
Subject: Re: Usability, MySQL, Postgresql.org, gborg, contrib,
Previous:From: Simon RiggsDate: 2004-04-27 22:46:34
Subject: Re: [HACKERS] What can we learn from MySQL?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group