PostgreSQL in 与 = any 的SQL语法异同与性能优化

2023-08-17 23:38| 来源: 网络整理| 查看: 265

标签

PostgreSQL , in , = any (array()) , hash table , subplan , initplan

背景

数据库SQL也算一门比较神奇的语言了，比如很多需求可以有不同的SQL来实现：

我之前有输出过一个IN的测试，这里面实际上也涉及到多个语法，实现同一个功能点。测试CASE是1亿 in 100万的多种写法的性能差异。

《HTAP数据库 PostgreSQL 场景与性能测试之 25 - (OLTP) IN , EXISTS 查询》

例如下面三个QUERY的语义就是一样的

select * from tbl where id in (select id from t); select * from tbl where exists (select 1 from t where t.id=tbl.id); select * from tbl where id = any (array( select id from t ));

但是不同的SQL，数据库可能会选择不一样的执行计划，并且执行效率可能千差万别。

几个例子

1、创建测试表，模拟1万 IN 100万的操作。

postgres=# create table t(id int); CREATE TABLE postgres=# insert into t select generate_series(1,100*10000); INSERT 0 1000000

2、我们看一看不同写法的执行计划如何：

postgres=# explain select n = any(array(select id from t)) from generate_series(1,10000) as n; QUERY PLAN --------------------------------------------------------------------------------- Function Scan on generate_series n (cost=14425.00..14447.50 rows=1000 width=1) InitPlan 1 (returns $0) -> Seq Scan on t (cost=0.00..14425.00 rows=1000000 width=4) (3 rows) postgres=# explain select n in (select id from t) from generate_series(1,10000) as n; QUERY PLAN --------------------------------------------------------------------------------- Function Scan on generate_series n (cost=16925.00..16937.50 rows=1000 width=1) SubPlan 1 -> Seq Scan on t (cost=0.00..14425.00 rows=1000000 width=4) (3 rows)

3、你会发现两个语法用了不同的执行计划，一个是InitPlan, 一个是SubPlan.

对于IN的写法，work_mem参数会直接影响性能，work_mem的大小决定了subquery是否要装载到hash table。

postgres=# set work_mem ='1MB'; SET postgres=# explain select n in (select id from t) from generate_series(1,10000) as n; QUERY PLAN -------------------------------------------------------------------------------- Function Scan on generate_series n (cost=0.00..12916012.50 rows=1000 width=1) SubPlan 1 -> Materialize (cost=0.00..23332.00 rows=1000000 width=4) -> Seq Scan on t (cost=0.00..14425.00 rows=1000000 width=4) (4 rows) postgres=# set work_mem ='100MB'; SET postgres=# explain select n in (select id from t) from generate_series(1,10000) as n; QUERY PLAN --------------------------------------------------------------------------------- Function Scan on generate_series n (cost=16925.00..16937.50 rows=1000 width=1) SubPlan 1 -> Seq Scan on t (cost=0.00..14425.00 rows=1000000 width=4) (3 rows) if (subquery) { /* Generate Paths for the ANY subquery; we'll need all rows */ subroot = subquery_planner(root->glob, subquery, root, false, 0.0); /* Isolate the params needed by this specific subplan */ plan_params = root->plan_params; root->plan_params = NIL; /* Select best Path and turn it into a Plan */ final_rel = fetch_upper_rel(subroot, UPPERREL_FINAL, NULL); best_path = final_rel->cheapest_total_path; plan = create_plan(subroot, best_path); /* Now we can check if it'll fit in work_mem */ /* XXX can we check this at the Path stage? */ if (subplan_is_hashable(plan)) { SubPlan *hashplan; AlternativeSubPlan *asplan; /* OK, convert to SubPlan format. */ hashplan = castNode(SubPlan, build_subplan(root, plan, subroot, plan_params, ANY_SUBLINK, 0, newtestexpr, false, true)); /* Check we got what we expected */ Assert(hashplan->parParam == NIL); Assert(hashplan->useHashTable); /* build_subplan won't have filled in paramIds */ hashplan->paramIds = paramIds; /* Leave it to the executor to decide which plan to use */ asplan = makeNode(AlternativeSubPlan); asplan->subplans = list_make2(result, hashplan); result = (Node *) asplan; } } /* * subplan_is_hashable: can we implement an ANY subplan by hashing? */ static bool subplan_is_hashable(Plan *plan) { double subquery_size; /* * The estimated size of the subquery result must fit in work_mem. (Note: * we use heap tuple overhead here even though the tuples will actually be * stored as MinimalTuples; this provides some fudge factor for hashtable * overhead.) */ subquery_size = plan->plan_rows * (MAXALIGN(plan->plan_width) + MAXALIGN(SizeofHeapTupleHeader)); if (subquery_size > work_mem * 1024L) return false; return true; }

代码里面注释中，针对in, exists, any的subplan优化器实现也有一些介绍，涉及到性能相关：

实际上exists这里有提到，匹配到第一条就结束，所以评估是否使用哈希表时可能需要的容量很小。

/* * For an EXISTS subplan, tell lower-level planner to expect that only the * first tuple will be retrieved. For ALL and ANY subplans, we will be * able to stop evaluating if the test condition fails or matches, so very * often not all the tuples will be retrieved; for lack of a better idea, * specify 50% retrieval. For EXPR, MULTIEXPR, and ROWCOMPARE subplans, * use default behavior (we're only expecting one row out, anyway). * * NOTE: if you change these numbers, also change cost_subplan() in * path/costsize.c. * * XXX If an ANY subplan is uncorrelated, build_subplan may decide to hash * its output. In that case it would've been better to specify full * retrieval. At present, however, we can only check hashability after * we've made the subplan :-(. (Determining whether it'll fit in work_mem * is the really hard part.) Therefore, we don't want to be too * optimistic about the percentage of tuples retrieved, for fear of * selecting a plan that's bad for the materialization case. */ in vs = any vs exists性能对比

1、in, work_mem装不下subquery

postgres=# set work_mem ='64kB'; postgres=# explain analyze select n in (select id from t) from generate_series(1,10000) as n; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Function Scan on generate_series n (cost=0.00..12916012.50 rows=1000 width=1) (actual time=1.321..11484.646 rows=10000 loops=1) SubPlan 1 -> Materialize (cost=0.00..23332.00 rows=1000000 width=4) (actual time=0.003..0.619 rows=5000 loops=10000) -> Seq Scan on t (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.014..1.800 rows=10000 loops=1) Planning time: 0.091 ms Execution time: 11485.905 ms (6 rows)

2、in, work_mem装下了subquery

postgres=# set work_mem ='64MB'; SET postgres=# explain (analyze,verbose,timing,costs,buffers) select n in (select id from t) from generate_series(1,10000) as n; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- Function Scan on pg_catalog.generate_series n (cost=16925.00..16937.50 rows=1000 width=1) (actual time=497.142..500.701 rows=10000 loops=1) Output: (hashed SubPlan 1) Function Call: generate_series(1, 10000) Buffers: shared hit=4425 SubPlan 1 -> Seq Scan on public.t (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.024..124.703 rows=1000000 loops=1) Output: t.id Buffers: shared hit=4425 Planning time: 0.085 ms Execution time: 507.427 ms (10 rows)

3、= any, work_mem很小无所谓，因为不涉及hashtable

postgres=# set work_mem ='64kB'; SET postgres=# explain (analyze,verbose,timing,costs,buffers) select n = any(array(select id from t)) from generate_series(1,10000) as n; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- Function Scan on pg_catalog.generate_series n (cost=14425.00..14447.50 rows=1000 width=1) (actual time=233.871..446.120 rows=10000 loops=1) Output: (n.n = ANY ($0)) Function Call: generate_series(1, 10000) Buffers: shared hit=4425, temp read=19 written=18 InitPlan 1 (returns $0) -> Seq Scan on public.t (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.014..119.976 rows=1000000 loops=1) Output: t.id Buffers: shared hit=4425 Planning time: 0.085 ms Execution time: 447.666 ms (10 rows)

4、exists, work_mem需求量较少(exists由于优化器在匹配到1条后即刻返回，所以会选择使用索引，性能就非常好。)

postgres=# set work_mem ='64kB'; SET postgres=# explain (analyze,verbose,timing,costs,buffers) select exists (select 1 from t where t.id=n.n) from generate_series(1,10000) as n; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Function Scan on pg_catalog.generate_series n (cost=0.00..2852.50 rows=1000 width=1) (actual time=1.172..18.893 rows=10000 loops=1) Output: (SubPlan 1) Function Call: generate_series(1, 10000) Buffers: shared hit=40027, temp read=19 written=18 SubPlan 1 -> Index Only Scan using idx_t_1 on public.t (cost=0.42..2.84 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10000) Index Cond: (t.id = n.n) Heap Fetches: 10000 Buffers: shared hit=40027 Planning time: 0.118 ms Execution time: 19.902 ms (11 rows) postgres=# set work_mem ='64MB'; SET postgres=# explain (analyze,verbose,timing,costs,buffers) select exists (select 1 from t where t.id=n.n) from generate_series(1,10000) as n; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Function Scan on pg_catalog.generate_series n (cost=0.00..2852.50 rows=1000 width=1) (actual time=0.642..17.635 rows=10000 loops=1) Output: (alternatives: SubPlan 1 or hashed SubPlan 2) Function Call: generate_series(1, 10000) Buffers: shared hit=40027 SubPlan 1 -> Index Only Scan using idx_t_1 on public.t (cost=0.42..2.84 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10000) Index Cond: (t.id = n.n) Heap Fetches: 10000 Buffers: shared hit=40027 SubPlan 2 -> Seq Scan on public.t t_1 (cost=0.00..14425.00 rows=1000000 width=4) (never executed) Output: t_1.id Planning time: 0.129 ms Execution time: 18.612 ms (14 rows)

5、如果把索引干掉，exists性能就会下降了，同时性能也和是否使用哈希表有关。

postgres=# drop index idx_t_1; postgres=# set work_mem ='64kB'; SET postgres=# explain (analyze,verbose,timing,costs,buffers) select exists (select 1 from t where t.id=n.n) from generate_series(1,10000) as n; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Function Scan on pg_catalog.generate_series n (cost=0.00..16925010.00 rows=1000 width=1) (actual time=1.072..3036.590 rows=10000 loops=1) Output: (SubPlan 1) Function Call: generate_series(1, 10000) Buffers: shared hit=226260, temp read=19 written=18 SubPlan 1 -> Seq Scan on public.t (cost=0.00..16925.00 rows=1 width=0) (actual time=0.303..0.303 rows=1 loops=10000) Filter: (t.id = n.n) Rows Removed by Filter: 5000 Buffers: shared hit=226260 Planning time: 0.087 ms Execution time: 3037.904 ms (11 rows) postgres=# set work_mem ='64MB'; SET postgres=# explain (analyze,verbose,timing,costs,buffers) select exists (select 1 from t where t.id=n.n) from generate_series(1,10000) as n; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Function Scan on pg_catalog.generate_series n (cost=0.00..16925010.00 rows=1000 width=1) (actual time=517.150..521.142 rows=10000 loops=1) Output: (alternatives: SubPlan 1 or hashed SubPlan 2) Function Call: generate_series(1, 10000) Buffers: shared hit=4425 SubPlan 1 -> Seq Scan on public.t (cost=0.00..16925.00 rows=1 width=0) (never executed) Filter: (t.id = n.n) SubPlan 2 -> Seq Scan on public.t t_1 (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.027..127.111 rows=1000000 loops=1) Output: t_1.id Buffers: shared hit=4425 Planning time: 0.098 ms Execution time: 527.986 ms (13 rows) 小结

1、使用= any的写法，不会走subplan，因此不涉及hash table的问题。和work_mem设置大小无关。性能比较暴力，特别是当它不是在subquery里面时，性能贼好。

很多场景都可以使用，例如update limit, delete limit(阅后即焚），又或者就是简单的IN查询需求。

delete from tbl where ctid = any(array( select ctid from tbl where xxx limit xxx )); update tbl set xxx=xxx where ctid = any(array( select ctid from tbl where xxx limit xxx )); select * from tbl where id = any (array( query.... ));

推荐指数，五星。

2、exists，由于优化器会默认它只需要搜索到1条命中目标就不搜了，所以优化器评估是否使用hash table时，需要的内存相对较少，即使较小的work_mem也可能使用hashtable。

推荐指数，四星。

3、in ()，当出现在subquery中时，优化器评估这个subquery是否要构建哈希TABLE，直接和subquery的大小相关，所以需要较大的work_mem才会选择使用hashtable。

推荐指数，三星。

最后，由于这些SQL语义都相同，在内核优化时，可以考虑做一些QUERY REWRITE，来优化这样的SQL。

这样的话，用户可以不改SQL，就达到提高效率的目的。

感谢为此付出努力的所有PostgreSQL内核开发的小伙伴们。

【本文地址】

公司简介

联系我们