postgresql9.5 materialized View Test

A view is a database that stores only the query statements that define the view (the content is generated when the query is queried), and the materialized view is a view of the content after its query statement is queried and stored (the content is generated when the materialized view is refreshed and the data can be modified. Independent). Because materialized views are a materialized table structure of a view, the data in it is refreshed when the query is created, and when the original data is updated and modified, the data is inconsistent if the materialized view's table is not updated. Increased incremental refresh starting from 9.4, support for materialized views in version 9.5 also supports indexes, modifies table spaces, and specifies user access rights

postgresql9.5 materialized View test:

Search Package:sudo Apt-cache Search dtrace-*

Dtrac && readline install:sudo apt-get install Systemtap-sdt-dev libssl-dev libpam-dev libxml2-dev libxslt-d EV libtcl8.4 Libperl-dev Python-dev

./configure--prefix=/home/pg5/pgsql9.5-devel--with-port=5433--with-perl--without-tcl--with-python-- With-openssl--with-pam--without-ldap--with-libxml--with-libxslt--enable-thread-safety--with-blocksize=32-- Enable-dtrace--enable-debug

Make

sudo make install

CD PostgreSQL Source directory/contrib

Make

sudo make install (use FDW later)

The environment variables of the. BASHRC are to be configured:

Export Pghome=/home/pg5/pgsql9.5-develexport path= $PATH: $PGHOME/binexport pgdata=/home/pg5/dataexport PGUSER= Pg5export pgport=5433

test data:

Create Database Eachma;create table TBL (id int primary key,info text,crt_time timestamp); insert into TBL Select generate_s Eries (1,100000), MD5 (Random ():: Text), Clock_timestamp (); Create materialized view Tbl_view as SELECT * from TBL where id<1000 with no data; Create materialized view Tbl_view1 as SELECT * from TBL with no data; Create unique index idx_tbl_view_id on Tbl_view (ID); Create unique index idx_tbl_view1_id on tbl_view1 (ID); Refresh materialized view Tbl_view; Refresh materialized view tbl_view1; \timing #打开事务执行时间

incremental refresh; Refresh materialized view concurrently tbl_view1;

Non-incremental refresh:refresh materialized view tbl_view1;

See materialized View chart in PG:

SELECT * from Pg_matviews;

eachma=# select * from pg_matviews;-[ record 1 ]+------------------------- schemaname   | publicmatviewname  | tbl_viewmatviewowner |  Pg5tablespace   |hasindexes   | tispopulated  | tdefinition    |  select tbl.id,         +              |      tbl.info,           +              |     tbl.crt_time         +             |     FROM tbl              +             |   where  (tbl.id <  -[ record 2 ]+-------------------------schemaname   |  publicmatviewname  | tbl_view1matviewowner | pg5tablespace   | hasindexes   | tispopulated  | tdefinition   |   select tbl.id,         +              |     tbl.info,            +              |     tbl.crt_time        +              |    from tbl ; Time: 0.758 ms

Incremental refreshes do not lock the table and block other queries. However, a non-incremental refresh of the view locks the table. The pros and cons are different, the former does not lock the table, but the execution takes longer, because it is a join query requires a comparison of data , in exchange for the time for the query lock. So that it does not affect the query work of materialized views . The latter's execution wait time is relatively short. But other queries need to wait for the refresh to complete:

Incremental refresh:

eachma=# begin; begintime:0.117 mseachma=# Refresh materialized view concurrently tbl_view1; REFRESH materialized viewtime:2085.527 mseachma=# commit; committime:2.718 mseachma=# End

#非增量刷新eachma =# begin; begintime:0.104 mseachma=# Refresh materialized view tbl_view1; REFRESH materialized viewtime:209.312 mseachma=# commit; committime:9.777 mseachma=# end; Warning:there is no transaction in progresscommittime:0.318 MS

PG is a materialized view that supports external tables. For example, Oracle has a table of data that we want to need, but generally we need to pass ODBC or dump out. But because PG supports external tables (Fdw,dblink). By creating a data that we need in Oracle, you can create an external table and then create a materialized view of the external table, which will also reduce the copy of the data and update the view when Oracle has data updates. Note, however, that the refresh of the table increment of the materialized view is consistent with the remote table Rule (index)

eachma=# CREATE EXTENSION POSTGRES_FDW;

Installation succeeded:

eachma=# \df                                   List of functions schema |          name           | Result data type |  Argument data types |  type--------+------------------------+------------------+----- ----------------+-------- public | postgres_fdw_handler   | fdw_handler       |                      | normal public | postgres_fdw_ validator | void             |  text[], oid         | normal 

Reference: http://www.postgresql.org/docs/9.5/static/postgres-fdw.html

postgresql9.5 materialized View Test