Thread: Progress Report on Materialized Views
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I've implemented a pretty simple Materialized Views scheme. It's not terribly complicated, and it works quite well. This is what I do. 0) Initialize the materialized view environment. This will allow the system to track which tables and views are part of a materialized view, as well as when the last time it was refreshed was. CREATE TABLE matviews ( mv_name NAME NOT NULL , mv_view NAME NOT NULL , last_refresh TIMESTAMP ); 1) Create a view. This will be what the materialized view should keep in sync with. One column must be unique and non-null. This will be the primary key of the materialized view. 2) Create a table -- the materialized view -- from the view. I've wrapped this up into a pl/PgSQL function. Pseudo-code is basically:- Create a table, the materialized view.- Select everything fromthe corresponding view into the materialized view.- Insert a row into matviews, last_refresh = now(). 3) Create a function called "<mv name>_refresh_row(<primary key type>)". This will:- Delete the row from the materialized view with that primary key- Select the row with that primary key from theview and insert it into the materialized view. 4) If there is any sort of time-dependence, create a function called "<mv name>_refresh()". This will find all the rows that have changed due to the time-dependence. It uses "last_refresh" from the matviews table, and "now()" to determine the timespan. This function needs to be called periodically. 5) Create triggers on all tables that contribute to the view.- An insert trigger, that will discover the primary key(s) thatthe inserted row will affect, and refreshes those rows (using the *_refresh_row function)- An update trigger, that will discover all the primary key(s) that the updated row will affect, and refreshes those rows. Note that the primary keys may be different if the column that determines the primary key is changing.- A delete trigger, that will dicover all the primary key(s) that the updated row will affect, and refreshes those rows. The system has been running in a production environment for over a week, with only one problem: deadlock when we were inserting vast amounts of new data. Adding appropriate indexes to the materialized views has reduced the query times for some of our most important queries to 1/300 of the original time. 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. My next step is to write a generic algorithm for handling the tasks mentioned in step s 4 and 5. I've written these by hand so far, because I can tell which columns of the updated/inserted/deleted row determine which rows in the materialized view will be affected. Any help in this area would be greatly appreciated. After that, I would like to investigate whether or not it is possible to register a function to be called when the transaction is committed. That way, the materialized view update can be deferred until the transaction is complete. This would enhance performance. - -- Jonathan Gardner jgardner@jonathangardner.net -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFANnqdqp6r/MVGlwwRAveMAJ0TsLyG2w3wlOv+LvtbePvzmkueFwCeJxlX arJKaqFBxGOuXS0L4DJeIwQ= =FGig -----END PGP SIGNATURE-----
>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
"Simon Riggs" <simon@2ndquadrant.com> writes:
> 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.
Oh? As far as I can tell, TPC-H forbids use of materialized views.
See sections 1.5.6 and 1.5.7 of the spec. The effect of the fine print
seems to be that the only way you are allowed to store extra copies of
data is as indexes over columns that are primary keys, foreign keys,
or date columns.
regards, tom lane
>Tom Lane > "Simon Riggs" <simon@2ndquadrant.com> writes: > > 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. > > Oh? As far as I can tell, TPC-H forbids use of materialized views. > See sections 1.5.6 and 1.5.7 of the spec. The effect of the fine print > seems to be that the only way you are allowed to store extra copies of > data is as indexes over columns that are primary keys, foreign keys, > or date columns. Sorry, I wasn't very clear there. I'm very happy that you're looking at this area and are able to slap my imprecision into shape so succinctly. You are 100% right: MVs are unambiguously not allowed as part of the TPC-H spec - what 1.5.7 shows is how useful MVs are: they've had to ban them! My take on the reason we now have MVs in all of the major commercial DBMS is because they weren't directly banned in the original TPC-D spec. [ http://www.tpc.org/tpch/spec/tpch2.1.0.pdf ] For me, there are two issues: i) passing the TPC-H test ii) dealing with a real-world workload IMHO, TPC-H is very much a real-world workload, so the difference is: i) coping with a TPC-H style workload when you have no a priori knowledge of what might be performed, and when: that is the heart of the TPC-H test. I think it is important that we strive to succeed on the pure test since there always will be many queries you can't predict. Removing MVs from that was an important statement about the requirement to cope with ad-hoc queries. ii) coping with the same workload when you have learnt something about it - i.e. you are able to tune the system over time. That's where MVs come in. Of course, you can go too far here, so there needs to be some judgement about what constitutes a real-world MV scenario. For me, the issue isn't passing the test, but exceeding it. If Jonathan's MV work can make a noticeable improvement on the DBT-3 workload, then it will be worthwhile; this is likely to be intertwined with optimizer improvements required in other areas. However, overall it was very cheeky of me to presume to bring you or anybody else together on these things, so I'll butt out until I have time to contribute personally, Best Regards, Simon Riggs
"Simon Riggs" <simon@2ndquadrant.com> writes:
> You are 100% right: MVs are unambiguously not allowed as part of the
> TPC-H spec - what 1.5.7 shows is how useful MVs are: they've had to ban
> them!
Ah, now I get your point. You're quite right, the TPC-H queries taken
as a repetitive workload would be a good test case for materialized
views. I misunderstood you as suggesting that we'd want to push use
of MVs into the DBT-3 benchmark as such.
regards, tom lane