So sánh tốc độ truy vấn giữa PostgreSQL Table, View, materialized Views

Table of Contents

So sánh tốc độ truy vấn giữa PostgreSQL Table, View, materialized Views

Compare Speed of PostgreSQL Table, View, materialized Views

Make view

View

Create view ds_baiviet as
SELECT maso,tieude,nhom,luotxem,ngaycapnhat FROM "baiviet";

MATERIALIZED VIEW

CREATE MATERIALIZED VIEW mat_ds_baiviet as
SELECT maso,tieude,nhom,luotxem,ngaycapnhat FROM "baiviet";

REFRESH MATERIALIZED VIEW mat_ds_baiviet;

Muốn
REFRESH MATERIALIZED VIEW CONCURRENTLY mat_ds_baiviet;
thì
CREATE UNIQUE INDEX ON mat_ds_baiviet (maso);
CREATE UNIQUE INDEX ON mat_ds_baiviet (ngaycapnhat);

Compare

Pure table

Query

explain (buffers, analyze) SELECT maso,tieude,nhom,luotxem,ngaycapnhat FROM baiviet ORDER BY RANDOM() limit 20 offset 10;

Result

Limit (cost=8101.53..8101.58 rows=20 width=179) (actual time=314.863..314.880 rows=20 loops=1)
 Buffers: shared hit=1207 read=3021
 -> Sort (cost=8101.50..8332.06 rows=92222 width=179) (actual time=314.859..314.863 rows=30 loops=1)
 Sort Key: (random())
 Sort Method: top-N heapsort Memory: 38kB
 Buffers: shared hit=1207 read=3021
 -> Seq Scan on baiviet (cost=0.00..5377.78 rows=92222 width=179) (actual time=0.021..283.733 rows=91753 loops=1)
 Buffers: shared hit=1204 read=3021
Planning time: 0.458 ms
Execution time: 314.920 ms

View

Query

explain (buffers, analyze) SELECT maso,tieude,nhom,luotxem,ngaycapnhat FROM ds_baiviet ORDER BY RANDOM() limit 20 offset 10;

Result

Limit (cost=8101.53..8101.58 rows=20 width=179) (actual time=282.864..282.870 rows=20 loops=1)
 Buffers: shared hit=1239 read=2989
 -> Sort (cost=8101.50..8332.06 rows=92222 width=179) (actual time=282.861..282.864 rows=30 loops=1)
 Sort Key: (random())
 Sort Method: top-N heapsort Memory: 38kB
 Buffers: shared hit=1239 read=2989
 -> Seq Scan on baiviet (cost=0.00..5377.78 rows=92222 width=179) (actual time=0.032..257.428 rows=91753 loops=1)
 Buffers: shared hit=1236 read=2989
Planning time: 0.424 ms
Execution time: 282.911 ms

materialized Views

Query

explain (buffers, analyze) SELECT maso,tieude,nhom,luotxem,ngaycapnhat FROM mat_ds_baiviet ORDER BY RANDOM() limit 20 offset 10;

Result

Limit (cost=5817.81..5817.86 rows=20 width=150) (actual time=53.415..53.424 rows=20 loops=1)
 Buffers: shared hit=1964
 -> Sort (cost=5817.79..6047.17 rows=91753 width=150) (actual time=53.410..53.413 rows=30 loops=1)
 Sort Key: (random())
 Sort Method: top-N heapsort Memory: 39kB
 Buffers: shared hit=1964
 -> Seq Scan on mat_ds_baiviet (cost=0.00..3107.91 rows=91753 width=150) (actual time=0.025..27.988 rows=91753 loops=1)
 Buffers: shared hit=1961
Planning time: 0.313 ms
Execution time: 53.503 ms

Leave a Reply Cancel reply