sql - Postgres pg_trgm GIN 索引在特定连接中被忽略
问题描述
我有一个item
包含多个文本字段的表,例如name
, unique_attr
,category
等,所有这些我都使用 GIN (gin_trgm_ops) 索引进行了索引,以实现更快的ilike
查询,事实上,即使连接到表inventory_membership
,索引也会被使用和速度提高执行时间。我解释的输出:
explain analyze select i.* from item i
join inventory_membership im on im.inventory_id = i.inventory_id
where i.name ilike '%blu%' or unique_attr ilike '%blu%' or category ilike '%blu%'
or brand ilike '%blu%';
Hash Join (cost=98.64..4584.98 rows=87302 width=478) (actual time=4.258..30.393 rows=57584 loops=1)
Hash Cond: (i.inventory_id = im.inventory_id)
-> Bitmap Heap Scan on item i (cost=95.45..3584.23 rows=4982 width=478) (actual time=3.706..10.529 rows=3340 loops=1)
Recheck Cond: ((name ~~* '%blu%'::text) OR (unique_attr ~~* '%blu%'::text) OR (category ~~* '%blu%'::text) OR (brand ~~* '%blu%'::text))
Heap Blocks: exact=715
-> BitmapOr (cost=95.45..95.45 rows=5130 width=0) (actual time=3.622..3.622 rows=0 loops=1)
-> Bitmap Index Scan on item_name_idx (cost=0.00..42.97 rows=3596 width=0) (actual time=1.612..1.612 rows=3160 loops=1)
Index Cond: (name ~~* '%blu%'::text)
-> Bitmap Index Scan on item_unique_attr_idx (cost=0.00..12.01 rows=1 width=0) (actual time=0.586..0.586 rows=32 loops=1)
Index Cond: (unique_attr ~~* '%blu%'::text)
-> Bitmap Index Scan on item_category_idx (cost=0.00..22.78 rows=1437 width=0) (actual time=0.888..0.888 rows=1394 loops=1)
Index Cond: (category ~~* '%blu%'::text)
-> Bitmap Index Scan on item_brand_idx (cost=0.00..12.72 rows=96 width=0) (actual time=0.532..0.532 rows=42 loops=1)
Index Cond: (brand ~~* '%blu%'::text)
-> Hash (cost=1.97..1.97 rows=97 width=4) (actual time=0.059..0.060 rows=87 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on inventory_membership im (cost=0.00..1.97 rows=97 width=4) (actual time=0.010..0.032 rows=87 loops=1)
Planning Time: 0.924 ms
Execution Time: 42.093 ms
我们可以看到正在使用 、 和 GIN 索引来item_name_idx
索引item_unique_attr_idx
条件item_category_idx
。item_brand_idx
伟大的。
但是,当我加入另一个表(inventory
只有id
和name
列的表)时,索引会消失。解释:
explain analyze select i.* from item i
join inventory inv on inv.id = i.inventory_id
join inventory_membership im on im.inventory_id = i.inventory_id
where i.name ilike '%blu%' or unique_attr ilike '%blu%' or category ilike '%blu%' or brand
ilike '%blu%';
Hash Join (cost=4.67..1172.61 rows=60407 width=478) (actual time=0.775..121.787 rows=57584 loops=1)
Hash Cond: (inv.id = im.inventory_id)
-> Merge Join (cost=1.49..440.81 rows=4982 width=482) (actual time=0.111..101.857 rows=3340 loops=1)
Merge Cond: (i.inventory_id = inv.id)
-> Index Scan using item_inventory_id_idx on item i (cost=0.29..13946.60 rows=4982 width=478) (actual time=0.085..99.857 rows=3340 loops=1)
Filter: ((name ~~* '%blu%'::text) OR (unique_attr ~~* '%blu%'::text) OR (category ~~* '%blu%'::text) OR (brand ~~* '%blu%'::text))
Rows Removed by Filter: 34858
-> Sort (cost=1.20..1.22 rows=8 width=4) (actual time=0.020..0.025 rows=8 loops=1)
Sort Key: inv.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on inventory inv (cost=0.00..1.08 rows=8 width=4) (actual time=0.006..0.009 rows=8 loops=1)
-> Hash (cost=1.97..1.97 rows=97 width=4) (actual time=0.650..0.651 rows=87 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on inventory_membership im (cost=0.00..1.97 rows=97 width=4) (actual time=0.005..0.028 rows=87 loops=1)
Planning Time: 7.193 ms
Execution Time: 132.427 ms
你可以看到 GIN 索引消失了,解释使用的唯一索引是item_inventory_id_idx
- 这是常规的 FK BTREE 索引。此外,执行时间也过得很快。为什么?
解决方案
您注意到您主要对库存名称感兴趣,并且库存表中只有 8 行。8 行是查询计划器更喜欢 amerge join
而不是 的原因hash join
,这在两个表都很大时效果更好。合并连接需要inventory_id
排序列表中的 (这正是索引的含义),这意味着它不希望使用您的 GIN 索引,因为它认为这样效率会降低。
现在,没有数据,你可以做几件事,我不知道哪一个会更快。您已经尝试过的第一个是在 a 中获取库存名称scalar subquery
:
SELECT i.*, (select name from inventory where id = i.inventory_id) as inventoryName
FROM item i
JOIN inventory_membership im ON im.inventory_id = i.inventory_id
WHERE i.name ilike '%blu%' or unique_attr ilike '%blu%' or category ilike '%blu%'
or brand ilike '%blu%';
但这意味着这条select
语句被执行了 57k 次,每行一次。第二个是使用您拥有的查询,但看看更改i.inventory_id
为inv.id
in是否inventory_membership
会改变任何内容。
SELECT i.*, inv.name as inventoryName
FROM item i
JOIN inventory inv ON inv.id = i.inventory_id
JOIN inventory_membership im ON im.inventory_id = inv.id -- <- this changed
WHERE i.name ilike '%blu%' or unique_attr ilike '%blu%' or category ilike '%blu%'
or brand ilike '%blu%';
最后,正如它在这个问题中所说,您可能会在获取库存名称之前强制执行第一个查询,使用 CTE 或带有OFFSET 0
.
WITH my_items AS (
SELECT i.*
FROM item i
JOIN inventory_membership im ON im.inventory_id = i.inventory_id
WHERE i.name ilike '%blu%' or unique_attr ilike '%blu%' or category ilike '%blu%'
or brand ilike '%blu%'
)
SELECT i.*, inv.name as inventoryName
FROM my_items i
JOIN inventory inv ON inv.id = i.inventory_id
或者
SELECT i.*, inv.name as inventoryName
FROM (
SELECT i.*
FROM item i
JOIN inventory_membership im ON im.inventory_id = i.inventory_id
WHERE i.name ilike '%blu%' or unique_attr ilike '%blu%' or category ilike '%blu%'
or brand ilike '%blu%'
OFFSET 0 -- <- this forces the subquery to be evaluated separate from the rest of the query
) i
JOIN inventory inv ON inv.id = i.inventory_id
推荐阅读
- html - bootstrap.min.css.map 出现在奇怪的地方
- qt - QML 关于调用嵌套变量的问题
- unity3d - ScriptableObject,添加了一个字段,资产不更新,怎么办?
- python - 无法读取 npy 文件
- apache-spark - PySpark:在 Barplot 中使用 TransformedDStream
- r - 使用 for 循环重命名数据框中的变量
- julia - Pluto Notebook 中的变量输出
- c++ - 排序函数在 C++17 上返回错误的对象类型
- python - DataFrame 按 Enter 键拆分列
- docker - Artifactory / Docker - 从远程 repo 中提取会导致 artifcatory 和 docker 客户端出现异常