PostgreSQL 11 preview - 通用场景性能 增强 汇总

标签

PostgreSQL , 通用场景性能 , 增强 , 11

背景

PostgreSQL 11 通用场景性能增强。

E.1.3.1.5. General Performance

• Add Just-In-Time (JIT) compilation of some parts of query plans to improve execution speed (Andres Freund)

  提高OLAP性能（海量数据处理，多表达式计算场景），动态编译，提高效率，结合列存储，CPU向量计算性能更加。

  《PostgreSQL 11 preview - JIT接口放开》

  《PostgreSQL 11 preview - with_llvm JIT支持部署与试用》

• Allow bitmap scans to perform index-only scans when possible (Alexander Kuzmenkov)

  index only scan支持bitmapscan。

• Update the free space map during vacuum (Claudio Freire)

  This allows free space to be reused more quickly.

• Allow vacuum to avoid unnecesary index scans (Masahiko Sawada, Alexander Korotkov)

• Improve performance of committing multiple concurrent transactions (Amit Kapila)

  并发提交事务性能提升，实测高并发COMMIT比PG 10好很多。

• Reduce memory usage for queries using set-returning functions in their target lists (Andres Freund)

  降低调用srf函数的QUERY的内存使用。

• Allow postgres_fdw to push UPDATEs and DELETEs using joins to foreign servers (Etsuro Fujita)

  Previously only non-join UPDATEs and DELETEs were pushed.

  postgres_fdw外部表下推增强，PostgreSQL 11允许包含JOIN的update,delete SQL下推。

测试

create table t_loc1 (id int, info text);    
create table t_loc2 (id int, info text);    
    
create extension postgres_fdw;    
    
CREATE SERVER foreign_server    
  FOREIGN DATA WRAPPER postgres_fdw    
  OPTIONS (host '127.0.0.1', port '4000', dbname 'postgres');    
    
CREATE USER MAPPING FOR postgres    
        SERVER foreign_server    
        OPTIONS (user 'postgres', password 'password');    
    
CREATE FOREIGN TABLE ft_loc1 (    
        id integer,    
        info text    
)    
        SERVER foreign_server    
        OPTIONS (schema_name 'public', table_name 't_loc1');    
    
    
CREATE FOREIGN TABLE ft_loc2 (    
        id integer,    
        info text    
)    
        SERVER foreign_server    
        OPTIONS (schema_name 'public', table_name 't_loc2');    
    
set enable_mergejoin=off;    
set enable_hashjoin=off;    

PostgreSQL 11, select, update, delete join都下推。

postgres=# explain verbose select t1.* from ft_loc1 t1 join ft_loc2 t2 using (id);    
                                                   QUERY PLAN                                                       
----------------------------------------------------------------------------------------------------------------    
 Foreign Scan  (cost=100.00..166443.65 rows=319523 width=36)    
   Output: t1.id, t1.info    
   Relations: (public.ft_loc1 t1) INNER JOIN (public.ft_loc2 t2)    
   Remote SQL: SELECT r1.id, r1.info FROM (public.t_loc1 r1 INNER JOIN public.t_loc2 r2 ON (((r1.id = r2.id))))    
(4 rows)    
    
postgres=# explain verbose update ft_loc1 t1 set info=t2.info from ft_loc2 t2 where t1.id=t2.id;    
                                                  QUERY PLAN                                                      
--------------------------------------------------------------------------------------------------------------    
 Update on public.ft_loc1 t1  (cost=100.00..68647.09 rows=131545 width=102)    
   ->  Foreign Update  (cost=100.00..68647.09 rows=131545 width=102)    
         Remote SQL: UPDATE public.t_loc1 r1 SET info = r2.info FROM public.t_loc2 r2 WHERE ((r1.id = r2.id))    
(3 rows)    

PostgreSQL 10, select join下推，但是update,delete join没有下推。

postgres=# explain verbose select t1.* from ft_loc1 t1 join ft_loc2 t2 using (id);    
                                                   QUERY PLAN                                                       
----------------------------------------------------------------------------------------------------------------    
 Foreign Scan  (cost=100.00..10543.72 rows=19963 width=36)    
   Output: t1.id, t1.info    
   Relations: (public.ft_loc1 t1) INNER JOIN (public.ft_loc2 t2)    
   Remote SQL: SELECT r1.id, r1.info FROM (public.t_loc1 r1 INNER JOIN public.t_loc2 r2 ON (((r1.id = r2.id))))    
(4 rows)    
    
postgres=# explain verbose update ft_loc1 t1 set info=t2.info from ft_loc2 t2 where t1.id=t2.id;    
                                                                                                   QUERY PLAN                                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
 Update on public.ft_loc1 t1  (cost=100.00..4422.55 rows=8215 width=102)    
   Remote SQL: UPDATE public.t_loc1 SET info = $2 WHERE ctid = $1    
   ->  Foreign Scan  (cost=100.00..4422.55 rows=8215 width=102)    
         Output: t1.id, t2.info, t1.ctid, t2.*    
         Relations: (public.ft_loc1 t1) INNER JOIN (public.ft_loc2 t2)    
         Remote SQL: SELECT r1.id, r1.ctid, r2.info, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.id, r2.info) END FROM (public.t_loc1 r1 INNER JOIN public.t_loc2 r2 ON (((r1.id = r2.id)))) FOR UPDATE OF r1    
         ->  Nested Loop  (cost=200.00..24958.51 rows=8215 width=102)    
               Output: t1.id, t1.ctid, t2.info, t2.*    
               Join Filter: (t1.id = t2.id)    
               ->  Foreign Scan on public.ft_loc1 t1  (cost=100.00..182.27 rows=2409 width=10)    
                     Output: t1.id, t1.ctid    
                     Remote SQL: SELECT id, ctid FROM public.t_loc1 FOR UPDATE    
               ->  Materialize  (cost=100.00..133.87 rows=682 width=96)    
                     Output: t2.info, t2.*, t2.id    
                     ->  Foreign Scan on public.ft_loc2 t2  (cost=100.00..130.46 rows=682 width=96)    
                           Output: t2.info, t2.*, t2.id    
                           Remote SQL: SELECT id, info FROM public.t_loc2    
(17 rows)