PostgreSQL DBA(5) - PG 11 New Features#2
PG 11即將正式釋出,本節簡單介紹了PG 11的一些新特性,包括SQL語句方面的增強。
一、SQL語句
VACUUM/ANALYZE
支援同時對多個Table進行操作
VACUUM
testdb=# vacuum verbose t_hash1,t_hash2; INFO:vacuuming "public.t_hash1_1" INFO:"t_hash1_1": found 0 removable, 73 nonremovable row versions in 1 out of 1208 pages DETAIL:0 dead row versions cannot be removed yet, oldest xmin: 807 There 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_hash1_2" INFO:"t_hash1_2": found 0 removable, 108 nonremovable row versions in 1 out of 1211 pages DETAIL:0 dead row versions cannot be removed yet, oldest xmin: 807 There 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_hash1_3" INFO:"t_hash1_3": found 0 removable, 84 nonremovable row versions in 1 out of 1207 pages DETAIL:0 dead row versions cannot be removed yet, oldest xmin: 807 There 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_hash1_4" INFO:"t_hash1_4": found 0 removable, 122 nonremovable row versions in 1 out of 1210 pages DETAIL:0 dead row versions cannot be removed yet, oldest xmin: 807 There 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_hash1_5" INFO:"t_hash1_5": found 0 removable, 62 nonremovable row versions in 1 out of 1209 pages DETAIL:0 dead row versions cannot be removed yet, oldest xmin: 807 There 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_hash1_6" INFO:"t_hash1_6": found 0 removable, 42 nonremovable row versions in 1 out of 1212 pages DETAIL:0 dead row versions cannot be removed yet, oldest xmin: 807 There 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" INFO:"t_hash2": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL:0 dead row versions cannot be removed yet, oldest xmin: 807 There 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_hash1,t_hash3; ANALYZE
LIMIT語句下推
testdb=# drop table if exists t1; DROP TABLE testdb=# create table t1 (c1 int,c2 varchar(40),c3 varchar(40)); CREATE TABLE testdb=# 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 alias LINE 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.20 9 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 r ows=5 loops=3) Output: t1.c1, t1.c2, t1.c3 Sort Key: t1.c1 Sort Method: top-N heapsortMemory: 25kB Worker 0:Sort Method: top-N heapsortMemory: 25kB Worker 1:Sort Method: top-N heapsortMemory: 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 heapsortMemory: 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 INDEX testdb=# 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_beta1_en_20180525-1.pdf" target="_blank" rel="nofollow,noindex">PostgreSQL 11 New Features With Examples(Beta 1)