提问人:Tiancheng Liu 提问时间:7/7/2019 最后编辑:Bouke VersteeghTiancheng Liu 更新时间:9/9/2021 访问量:11960
Postgresql IN 与 ANY 运算符与子查询的性能差异
Postgresql IN vs ANY operator performance differences with subquery
问:
我有两个查询做同样的事情。 1
SELECT *
FROM "Products_product"
WHERE ("Products_product"."id" IN
(SELECT U0."product_id"
FROM "Products_purchase" U0
WHERE (U0."state" = 1
AND U0."user_id" = 5))
AND "Products_product"."state" IN (1,
6,
3)
AND UPPER("Products_product"."title" :: TEXT) LIKE UPPER('%toronto%'))
ORDER BY "Products_product"."title_index" ASC
LIMIT 10;
2
SELECT *
FROM "Products_product"
WHERE ("Products_product"."id" = ANY (ARRAY(
(SELECT U0."product_id"
FROM "Products_purchase" U0
WHERE (U0."state" = 1
AND U0."user_id" = 5))))
AND "Products_product"."state" IN (1,
6,
3)
AND UPPER("Products_product"."title" :: TEXT) LIKE UPPER('%toronto%'))
ORDER BY "Products_product"."title_index" ASC
LIMIT 10;
唯一的区别是第一次用于子查询,而第二次用于。但第二个明显比第一个快 10 倍左右。
我运行了解释,得到了以下两个结果:
1IN
=ANY(ARRAY())
Limit (cost=5309.92..5309.93 rows=1 width=1906) (actual time=3414.185..3414.190 rows=10 loops=1)
-> Sort (cost=5309.92..5309.93 rows=1 width=1906) (actual time=3414.184..3414.185 rows=10 loops=1)
Sort Key: "Products_product".title
Sort Method: quicksort Memory: 57kB
-> Nested Loop Semi Join (cost=92.66..5309.91 rows=1 width=1906) (actual time=3385.153..3414.099 rows=16 loops=1)
-> Bitmap Heap Scan on "Products_product" (cost=13.85..256.32 rows=61 width=1906) (actual time=3381.327..3384.430 rows=63 loops=1)
Recheck Cond: ((state = ANY ('{1,6,3}'::integer[])) AND (upper((title)::text) ~~ '%TORONTO%'::text))
Rows Removed by Index Recheck: 1
Heap Blocks: exact=64
-> Bitmap Index Scan on "Products_product_state_id_upper_idx" (cost=0.00..13.83 rows=61 width=0) (actual time=3381.001..3381.001 rows=64 loops=1)
Index Cond: ((state = ANY ('{1,6,3}'::integer[])) AND (upper((title)::text) ~~ '%TORONTO%'::text))
-> Bitmap Heap Scan on "Products_purchase" u0 (cost=78.82..82.84 rows=1 width=4) (actual time=0.467..0.467 rows=0 loops=63)
Recheck Cond: ((product_id = "Products_product".id) AND (user_id = 5))
Filter: (state = 1)
Heap Blocks: exact=16
-> BitmapAnd (cost=78.82..78.82 rows=1 width=0) (actual time=0.465..0.465 rows=0 loops=63)
-> Bitmap Index Scan on "Products_purchase_product_id" (cost=0.00..5.06 rows=84 width=0) (actual time=0.265..0.265 rows=30 loops=63)
Index Cond: (product_id = "Products_product".id)
-> Bitmap Index Scan on "Products_purchase_user_id" (cost=0.00..72.57 rows=3752 width=0) (actual time=0.242..0.242 rows=3335 loops=51)
Index Cond: (user_id = 5)
Planning time: 7.540 ms
Execution time: 3414.356 ms
(22 rows)
2
Limit (cost=7378.07..7378.07 rows=1 width=1906) (actual time=116.559..116.562 rows=10 loops=1)
InitPlan 1 (returns $0)
-> Index Scan using "Products_purchase_user_id" on "Products_purchase" u0 (cost=0.43..7329.83 rows=3752 width=4) (actual time=0.021..15.535 rows=3335 loops=1)
Index Cond: (user_id = 5)
Filter: (state = 1)
-> Sort (cost=48.24..48.25 rows=1 width=1906) (actual time=116.558..116.559 rows=10 loops=1)
Sort Key: "Products_product".title
Sort Method: quicksort Memory: 57kB
-> Bitmap Heap Scan on "Products_product" (cost=44.20..48.23 rows=1 width=1906) (actual time=116.202..116.536 rows=16 loops=1)
Recheck Cond: ((id = ANY ($0)) AND (upper((title)::text) ~~ '%TORONTO%'::text))
Filter: (state = ANY ('{1,6,3}'::integer[]))
Rows Removed by Filter: 2
Heap Blocks: exact=18
-> Bitmap Index Scan on "Products_product_id_upper_idx1" (cost=0.00..44.20 rows=1 width=0) (actual time=116.103..116.103 rows=18 loops=1)
Index Cond: ((id = ANY ($0)) AND (upper((title)::text) ~~ '%TORONTO%'::text))
Planning time: 1.054 ms
Execution time: 116.663 ms
(17 rows)
从 doc 来看,或 之间没有实质性区别。但是为什么我得到如此不同的结果。无论如何,它都有优势吗?IN
ANY
ANY
IN
更新: 有人指出这个问题可能与PostgreSQL中的IN与NOY运算符重复。 它们是同一个问题,但那个问题的答案并没有解决我的问题,因为除了这个答案之外,我还有一个更详细的案例。
但是每个的第二个变体并不等同于另一个变体。这 ANY 构造的第二个变体接受一个数组(必须是实际的 array type),而 IN 的第二个变体采用逗号分隔 值列表。这导致传递值时有不同的限制 在特殊情况下,还可能导致不同的查询计划:
在我的问题中,这两个问题都不是这种情况。我只是将一个数组作为子查询传递。我的情况与第一个 URL 正好相反。我的索引只用在 中,而不用在 中。所以基本上,这个答案并没有解决我的问题。ANY
IN
UPDATE2:
我更新了索引:.我可以确认两个查询的情况是相等的,这意味着索引存在在那里,但第一个没有使用它。CREATE INDEX ON "Products_product" USING GIST (state, id, upper((title) :: TEXT) gist_trgm_ops);
UPDATE3:
我只是在代码中删除。但结果是一样的。ARRAY
explain analyze SELECT *
FROM "Products_product"
WHERE ("Products_product"."id" = ANY(
(SELECT U0."product_id"
FROM "Products_purchase" U0
WHERE (U0."state" = 1
AND U0."user_id" = 5)))
AND "Products_product"."state" IN (1,
6,
3)
AND UPPER("Products_product"."title" :: TEXT) LIKE UPPER('%toronto%'))
ORDER BY "Products_product"."title" ASC
LIMIT 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=5309.92..5309.93 rows=1 width=1906) (actual time=228.980..228.983 rows=10 loops=1)
-> Sort (cost=5309.92..5309.93 rows=1 width=1906) (actual time=228.979..228.980 rows=10 loops=1)
Sort Key: "Products_product".title
Sort Method: quicksort Memory: 57kB
-> Nested Loop Semi Join (cost=92.66..5309.91 rows=1 width=1906) (actual time=216.392..228.913 rows=16 loops=1)
-> Bitmap Heap Scan on "Products_product" (cost=13.85..256.32 rows=61 width=1906) (actual time=214.332..215.260 rows=63 loops=1)
Recheck Cond: ((state = ANY ('{1,6,3}'::integer[])) AND (upper((title)::text) ~~ '%TORONTO%'::text))
Rows Removed by Index Recheck: 1
Heap Blocks: exact=64
-> Bitmap Index Scan on "Products_product_state_id_upper_idx" (cost=0.00..13.83 rows=61 width=0) (actual time=214.296..214.296 rows=64 loops=1)
Index Cond: ((state = ANY ('{1,6,3}'::integer[])) AND (upper((title)::text) ~~ '%TORONTO%'::text))
-> Bitmap Heap Scan on "Products_purchase" u0 (cost=78.82..82.84 rows=1 width=4) (actual time=0.215..0.215 rows=0 loops=63)
Recheck Cond: ((product_id = "Products_product".id) AND (user_id = 5))
Filter: (state = 1)
Heap Blocks: exact=16
-> BitmapAnd (cost=78.82..78.82 rows=1 width=0) (actual time=0.212..0.212 rows=0 loops=63)
-> Bitmap Index Scan on "Products_purchase_product_id" (cost=0.00..5.06 rows=84 width=0) (actual time=0.017..0.017 rows=30 loops=63)
Index Cond: (product_id = "Products_product".id)
-> Bitmap Index Scan on "Products_purchase_user_id" (cost=0.00..72.57 rows=3752 width=0) (actual time=0.239..0.239 rows=3335 loops=51)
Index Cond: (user_id = 5)
Planning time: 5.083 ms
Execution time: 229.904 ms
(22 rows)
我认为 or 不是这种情况,这只是 和 之间的区别ANY
ANY(ARRAY())
IN
ANY
答:
不同的计划不是由 vs. 引起的,而是由第二个查询中的子选择周围的附加项引起的。没有这个,计划是相同的。IN
= ANY
ARRAY()
不同之处在于,在缓慢的执行中,索引扫描需要很长时间,而在编辑的(完全相同的)计划中,相同的扫描速度很快:
慢:
-> Bitmap Index Scan on "Products_product_state_id_upper_idx" (cost=0.00..13.83 rows=61 width=0) (actual time=3381.001..3381.001 rows=64 loops=1)
Index Cond: ((state = ANY ('{1,6,3}'::integer[])) AND (upper((title)::text) ~~ '%TORONTO%'::text))
快:
-> Bitmap Index Scan on "Products_product_state_id_upper_idx" (cost=0.00..13.83 rows=61 width=0) (actual time=214.296..214.296 rows=64 loops=1)
Index Cond: ((state = ANY ('{1,6,3}'::integer[])) AND (upper((title)::text) ~~ '%TORONTO%'::text))
同样有趣的是,在慢速计划中,生成索引扫描的第一行需要 3 秒。
无论问题是什么,它都是暂时的。唯一想到的怀疑是被杀死的索引元组:大规模删除留下了许多索引元组,这些元组指向死堆元组,这些元组只需要在第一次扫描,因为之后它们被标记为死元组。
你有没有大规模删除?
评论
CREATE INDEX
where x in (1,2,3)
where x = any(array[1,2,3])
WHERE x IN (SELECT something)
WHERE x = ANY (SELECT something)
WHERE x = ANY (array(SELECT something))
array()
评论
where x in (1,2,3)
where x = any (array[1,2,3])
= ANY()
IN