标签
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向量计算性能更加。
-
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) 
