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

背景

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)    

digoal’s 大量PostgreSQL文章入口