Shulou(Shulou.com)06/01 Report--

PG 11 is about to be officially released, and this section provides a brief introduction to some of the new features of PG 11, including enhancements to SQL statements.

I. SQL statement

VACUUM/ANALYZE

Support for simultaneous operation of multiple 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.00s, system: 0.00s, 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.00s, system: 0.00s, 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.00s, system: 0.00s, elapsed: 0.00s.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.00s, system: 0.00s, elapsed: 0.00s.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.00s, system: 0.00s 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.00s, system: 0.00s, 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 tincture hash2 and Thornhash4 nametrum

LIMIT statement push down

Testdb=# drop table if exists T1 generate_series drop TABLEtestdb=# create table T1 (c1 int,c2 varchar (40), c3 varchar (40)); CREATE TABLEtestdb=# testdb=# insert into T1 select generate_series (1je 5000000), 'TEST' | | generate_series (1jue 5000000), generate_series (1jue 5000000) |' TEST';INSERT 0500000000 AS-need to add Alias (AS clause) testdb=# explain analyze verbose select * from (select * from T1 order by C1) limit 5 to the subquery 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)

Note the Sort Method: top-N heapsort Memory: 25kB in the execution plan.

Push the LIMIT clause down to the subquery and directly ask LIMIT 5 in the subquery, instead of asking LIMIT 5 after the subquery has been sorted.

Thus, the full sorting-> Top N problem is transformed into a direct Top N problem, and the execution efficiency is improved.

CREATE INDEX

When creating an index, add a new INCLUDE clause

Testdb=# create unique index idx_t2_c1 on T2 (C1) include (c2); CREATE INDEXtestdb=# explain analyze verbose select C1 Magazine c2 from T2 where C1 100 and c2 where 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

C1 unique index on T2, include c2 columns, does not affect the semantics, but when executing the query, the index can be scanned directly (Index Only Scan) to improve performance.

In addition to the above new features, PG 11 also enhances CREATE TABLE and other statements, please refer to Resources for details.

II. Reference materials

PostgreSQL 11 New Features With Examples (Beta 1)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.