Обсуждение: Efficiency of materialized views refresh in 9.3

On Fri, Jul 5, 2013 at 6:10 AM, Joe Van Dyk <joe@tanga.com> wrote:
> Hi,
>
> 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

And an example:
postgres=# create table aa (a int);
CREATE TABLE
postgres=# insert into aa values (1),(2);
INSERT 0 2
postgres=# create materialized view aam as select * from aa;
SELECT 2
postgres=# select * from aam;
 a
---
 1
 2
(2 rows)
postgres=# insert into aa values (3);
INSERT 0 1
postgres=# select * from aam;
 a
---
 1
 2
(2 rows)
postgres=# refresh materialized view aam;
REFRESH MATERIALIZED VIEW
postgres=# select * from aam;
 a
---
 1
 2
 3
(3 rows)

The REFRESH step takes an exclusive lock on the materialized view
during the time of its operation as far as I recall.

> Or is it more efficient? 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...).
postgres=# delete from aam where a = 2;
ERROR:  42809: cannot change materialized view "aam"
LOCATION:  CheckValidResultRel, execMain.c:1005

Thanks,
--
Michael

Michael Paquier <michael.paquier@gmail.com> wrote:
> Joe Van Dyk <joe@tanga.com> 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