本文共 5585 字，大约阅读时间需要 18 分钟。
PostgreSQL , 通用场景性能 , 增强 , 11
PostgreSQL 11 通用场景性能增强。
Add (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 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)
转载地址：http://fxlxl.baihongyu.com/