> Index Only Scan using pgbench_branches_pkey on pgbench_branches b (cost=0.14.0.17 rows=1 width=4) (actual time=0.009.0.009 rows=1 loops=563000)Īs you can see, it took over 24 seconds to gather the accounts with balances greater than 100. Postgres=# explain analyze select * from account_balances Take, for example, a view created on the pgbench dataset (scale 100, after ~150,000 transactions): postgres=# CREATE OR REPLACE VIEW account_balances AS +-+-+-+-+-+-īirthdate | timestamp without time zone | | | | plain |įor large data sets, sometimes VIEW does not perform well because it runs the underlying query **every** time the VIEW is referenced. If we peek under the hood, we discover that “roster” is a VIEW that combines two tables together:Ĭolumn | Type | Collation | Nullable | Default | Storage | Description Consider the following query: postgres=# SELECT * FROM roster

Most relational database systems provide the functionality to create a VIEW, which basically acts like a shortcut or macro. SUMMARY: This article introduces MATERIALIZED VIEW in PostgreSQL and discusses its functionality with examples.

Create view psql