我有一张名为item
的表格,其中有多个文本字段,如name
、unique_attr
、category
等。我使用GIN(gin_trgm_ops)索引对它们进行了索引,以加快ilike
查询速度,甚至在连接到一个名为inventory_membership
的表格时,也使用了这些索引,加快了执行时间。我的explain输出如下:
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
我们可以看到,
item_name_idx
、item_unique_attr_idx
、item_category_idx
和item_brand_idx
GIN索引被用于索引条件。很好。然而,当我加入另一个表(
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索引已经消失,而explain所使用的唯一索引是item_inventory_id_idx
——这是普通的外键BTREE索引。
此外,执行时间大幅上升。为什么呢?
inventory
表中的行数较少,那么这可能解释了为什么使用合并连接而不是哈希连接 - 这就是执行时间如此之高的原因。另外,请将查询计划作为字符串添加,现在我们无法看到Seq扫描的实际时间。 - Ruben Helsloot