>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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html