Michael Paquier <michael.paquier@xxxxxxxxx> wrote: > Joe Van Dyk <joe@xxxxxxxxx> wrote: >> Is refreshing a materialized view in 9.3 basically: >> >> delete from mat_view; >> insert into mat_view select * from base_view; > Nope. Here is some documentation: > http://www.postgresql.org/docs/devel/static/rules-materializedviews.html A REFRESH always re-runs the query which was used to define the materialized view. In 9.3, that is done while holding an AccessExclusiveLock, stored into a new heap in the tablespace the MV is using, reindexed, and moved into place to replace the previous heap. There is a pending patch for the following release to add a CONCURRENTLY option, which will generate the new heap in a temporary table, and use DELETE, UPDATE, and INSERT statements ("under the covers") to modify the original heap with just the differences -- it will not delete and re-insert all rows. In benchmarks it appears that when few rows are changed, the pending option is faster, since most of the work is done in temporary tables. If more than a small percentage of the rows change, the heap replacement will be hard to beat for REFRESH performance. >> Or is it more efficient? I would expect that a DELETE of all rows followed by an INSERT of all rows would be slower than the above in all cases, unless every single row is different and the differences generally include an indexed column. In that case the concurrent approach would perform the same as what you describe. >> If no rows have changed, will new tuples be written on a >> refresh? > Materialized views in 9.3 have no support DML (which would be > used for incremental updates? Someone correct me here if I'm > wrong...). Correct. 9.3 does not use DML for any MV changes. I plan to use DML internally for both REFRESH MATERIALIZED VIEW CONCURRENTLY and incremental maintenance based on the MV definition. I expect that direct user DML against a MV will continue to be prohibited so that incremental maintenance using the MV definition can be reliable. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
