PostgreSQL DBA(5) - PG 11 New Features#2

PG 11即将正式发布，本节简单介绍了PG 11的一些新特性，包括SQL语句方面的增强。

一、SQL语句

VACUUM/ANALYZE
支持同时对多个Table进行操作
VACUUM

testdb=# vacuum verbose t_hash2,t_hash3;INFO:  vacuuming "public.t_hash2_1"INFO:  "t_hash2_1": found 0 removable, 73 nonremovable row versions in 1 out of 1208 pagesDETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 807There were 0 unused item pointers.Skipped 0 pages due to buffer pins, 0 frozen pages.0 pages are entirely empty.CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.INFO:  vacuuming "public.t_hash2_2"INFO:  "t_hash2_2": found 0 removable, 108 nonremovable row versions in 1 out of 1211 pagesDETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 807There were 0 unused item pointers.Skipped 0 pages due to buffer pins, 0 frozen pages.0 pages are entirely empty.CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.INFO:  vacuuming "public.t_hash2_3"INFO:  "t_hash2_3": found 0 removable, 84 nonremovable row versions in 1 out of 1207 pagesDETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 807There were 0 unused item pointers.Skipped 0 pages due to buffer pins, 0 frozen pages.0 pages are entirely empty.CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.INFO:  vacuuming "public.t_hash2_4"INFO:  "t_hash2_4": found 0 removable, 122 nonremovable row versions in 1 out of 1210 pagesDETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 807There were 0 unused item pointers.Skipped 0 pages due to buffer pins, 0 frozen pages.0 pages are entirely empty.CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.INFO:  vacuuming "public.t_hash2_5"INFO:  "t_hash2_5": found 0 removable, 62 nonremovable row versions in 1 out of 1209 pagesDETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 807There were 0 unused item pointers.Skipped 0 pages due to buffer pins, 0 frozen pages.0 pages are entirely empty.CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.INFO:  vacuuming "public.t_hash2_6"INFO:  "t_hash2_6": found 0 removable, 42 nonremovable row versions in 1 out of 1212 pagesDETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 807There were 0 unused item pointers.Skipped 0 pages due to buffer pins, 0 frozen pages.0 pages are entirely empty.CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.INFO:  vacuuming "public.t_hash3"INFO:  "t_hash3": found 0 removable, 0 nonremovable row versions in 0 out of 0 pagesDETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 807There were 0 unused item pointers.Skipped 0 pages due to buffer pins, 0 frozen pages.0 pages are entirely empty.CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.VACUUM

ANALYZE

testdb=# analyze t_hash2,t_hash4;ANALYZE

LIMIT语句下推

testdb=# drop table if exists t1;DROP TABLEtestdb=# create table t1 (c1 int,c2 varchar(40),c3 varchar(40));CREATE TABLEtestdb=# testdb=# insert into t1 select generate_series(1,5000000),'TEST'||generate_series(1,5000000),generate_series(1,5000000)||'TEST';INSERT 0 5000000-- 需要为子查询添加Alias(AS子句)testdb=# explain analyze verbose select * from (select * from t1 order by c1) limit 5;ERROR:  subquery in FROM must have an aliasLINE 1: explain analyze verbose select * from (select * from t1 orde...                                              ^HINT:  For example, FROM (SELECT ...) [AS] foo.testdb=# explain analyze verbose select * from (select * from t1 order by c1) as t limit 5;                                                                    QUERY PLAN                                                                    -------------------------------------------------------------------------------------------------------------------------------------------------- Limit  (cost=196243.41..196244.04 rows=5 width=200) (actual time=8238.213..8238.216 rows=5 loops=1)   Output: t1.c1, t1.c2, t1.c3   ->  Gather Merge  (cost=196243.41..321021.75 rows=1069454 width=200) (actual time=8238.206..8238.209 rows=5 loops=1)         Output: t1.c1, t1.c2, t1.c3         Workers Planned: 2         Workers Launched: 2         ->  Sort  (cost=195243.38..196580.20 rows=534727 width=200) (actual time=3631.579..3631.580 rows=5 loops=3)               Output: t1.c1, t1.c2, t1.c3               Sort Key: t1.c1               Sort Method: top-N heapsort  Memory: 25kB               Worker 0:  Sort Method: top-N heapsort  Memory: 25kB               Worker 1:  Sort Method: top-N heapsort  Memory: 25kB               Worker 0: actual time=1337.287..1337.288 rows=5 loops=1               Worker 1: actual time=1336.033..1336.034 rows=5 loops=1               ->  Parallel Seq Scan on public.t1  (cost=0.00..42014.27 rows=534727 width=200) (actual time=4.358..2664.821 rows=1666667 loops=3)                     Output: t1.c1, t1.c2, t1.c3                     Worker 0: actual time=0.056..763.166 rows=651304 loops=1                     Worker 1: actual time=0.028..725.300 rows=655520 loops=1 Planning Time: 68.043 ms Execution Time: 8262.228 ms(20 rows)

注意执行计划中的 Sort Method: top-N heapsort Memory: 25kB.
把LIMIT子句下推到子查询中,在子查询中直接求LIMIT 5,而不是在子查询排序完毕后再求LIMIT 5.
从而将全排序-->求Top N问题转换为直接求Top N问题,提升执行效率.

CREATE INDEX
创建索引时,新增INCLUDE子句

testdb=# create unique index idx_t2_c1 on t2(c1) include(c2);CREATE INDEXtestdb=# explain analyze verbose select c1,c2 from t2 where c1=100 and c2='TEST';                                                         QUERY PLAN                                                          ----------------------------------------------------------------------------------------------------------------------------- Index Only Scan using idx_t2_c1 on public.t2  (cost=0.42..8.45 rows=1 width=12) (actual time=31.184..31.184 rows=0 loops=1)   Output: c1, c2   Index Cond: (t2.c1 = 100)   Filter: ((t2.c2)::text = 'TEST'::text)   Rows Removed by Filter: 1   Heap Fetches: 1 Planning Time: 0.108 ms Execution Time: 31.216 ms

t2上的c1唯一索引,include了c2列,不影响语义但在执行查询时,可以直接扫描索引(Index Only Scan),从而提升性能.

除了以上几个新特性,PG 11还对CREATE TABLE等语句有所增强,详细请参照参考资料.

二、参考资料

PostgreSQL 11 New Features With Examples(Beta 1)