Re: Progress Report on Materialized Views

>Jonathan M. Gardner
> I've implemented a pretty simple Materialized Views scheme. It's not
> terribly complicated, and it works quite well.

Exciting news - excellent work. Starting simple was the right approach!

> There were some issues with the time-sensitivity of the queries. For
> instance, our list of members will decrease as time goes along due to
the
> expiration date of accounts. Because we were running the refresh once
a
> day, there were a few hours of the day where the materialized view
would
> say that the person is a member, but the actual data says he is not.
We
> rewrote our code to pull everything from the materialized view,
greatly
> simplifying the code, and also increasing performance.

That's just "part of the package" of using Materialized Views. That is
an acceptable trade-off for the performance gains realised.

> My next step...

Could I suggest that your next step is to sync up with the work being
done on tuning the DBT-3 query workload? As I'm sure you're aware, that
is very similar to TPC-H workload, where most of the commercial RDBMS
vendors utilise Materialized Views to enhance certain queries. Focusing
on that workload may then suggest to you what the next steps to take
are, now that you have solved the specific problems of your own
workloads, though using a generic approach. I think ... Mark Wong, Josh
Berkus and Tom Lane are currently involved with DBT-3 testing on the
OSDL test environment.

Materialized Views and improved join-ordering are the next two best
angles of attack on the DBT-3 workload, IMHO.

I very much look forward to further news.

Best Regards, Simon Riggs