Postgres中的pg_trgm GIN索引在特定连接中被忽略

Question

Postgres中的pg_trgm GIN索引在特定连接中被忽略

4

我有一张名为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索引。此外，执行时间大幅上升。为什么呢？

- Ognjen Mišić

1

每个表中有多少行？如果inventory表中的行数较少，那么这可能解释了为什么使用合并连接而不是哈希连接 - 这就是执行时间如此之高的原因。另外，请将查询计划作为字符串添加，现在我们无法看到Seq扫描的实际时间。 - Ruben Helsloot

1

@RubenHelsloot 啊，对了，只有几个库存 - 总共8或9个。大约有4k个条目。至于查询计划，我马上就会编辑帖子。 - Ognjen Mišić

举个例子，类似的事情在这里也发生过（https://dba.stackexchange.com/questions/181674/undesirable-nest-loop-vs-hash-join-in-postgresql-9-6）。唯一的区别是，他们期望行数的偏移更大。然而，将“item + where”放入子查询或CTE中对您也可能有所帮助。 - Ruben Helsloot

啊，你的意思是我不加入库存而是子查询它。这样或许可以，我会试一下。另外，上面有个修正，我大约有4万个项目，不是4千个 :D - Ognjen Mišić

@RubenHelsloot，由于我主要对库存中的“名称”感兴趣，所以我现在已经将其索引化（作为常规btree可以吗？），并且“explain analyze select i.*，（select name as inventoryName from inventory where id = i.inventory_id）from item i...”的执行时间有点长（168.170毫秒），但计划时间为0.974毫秒。与旧的132.427执行时间和7.193计划时间相比，这是什么意思？此外，我终于看到我的GIN索引用在了我的“items”上！ - Ognjen Mišić

显示剩余2条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ruben Helsloot · Accepted Answer

您提到对库存名称感兴趣，而库存表中只有8行。由于只有8行，查询规划器更喜欢使用“合并连接”而不是“哈希连接”，后者在两个表都很大时效果更好。合并连接需要一个按排序列表（正是索引）排列的“inventory_id”，这意味着它不愿使用GIN索引，因为它认为那样效率较低。

现在，在没有数据的情况下，有几件事情可以做，我无法确定哪种方法更快。首先，您已经尝试过的是在“标量子查询”中获取库存名称:

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语句会被执行57000次，每行一次。第二种方法是使用你之前提到的查询，但是尝试将i.inventory_id更改为inv.id在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%';

最后，正如this问题中所说的那样，您可以使用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