sql - 为什么 Postgres 执行顺序扫描，其中索引将返回 < 1% 的数据？

Question

我有 19 年的 Oracle 和 MySQL 经验(DBA 和开发人员)并且我是 Postgres 的新手，所以我可能遗漏了一些明显的东西。但我无法让此查询执行我想要的操作。

注意:此查询在 EngineYard Postgres 实例上运行。我没有立即意识到它设置的参数。此外，items 表中的 applicable_type 和 status 列是扩展类型 citext。

以下查询可能需要超过 60 秒才能返回行:

SELECT items.item_id, 
       CASE when items.sku is null then items.title else concat(item.title, ' (SKU: ', items.sku, ')') END title, 
       items.listing_status, items.updated_at, items.id, 
       items.sku, count(details.id) detail_count 
FROM "items" LEFT OUTER JOIN details ON details.applicable_id = items.id 
                                    and details.applicable_type = 'Item' 
                                    and details.status = 'Valid' 
                LEFT OUTER JOIN products ON products.id = items.product_id
WHERE "items"."user_id" = 3
GROUP BY items.id
ORDER BY title asc
LIMIT 25 OFFSET 0

details 表包含 650 万行。 LEFT OUTER JOIN 对 applicable_id 进行顺序扫描。在基数方面，该列在 650 万行中有 12 万种不同的可能性。

我在 details 上有一个 btree 索引，包含以下列:

applicable_id
applicable_type
status

但实际上，applicable_id 和 applicable_type 的基数较低。

我的解释分析是这样的:

Limit  (cost=247701.59..247701.65 rows=25 width=118) (actual time=28781.090..28781.098 rows=25 loops=1)
  ->  Sort  (cost=247701.59..247703.05 rows=585 width=118) (actual time=28781.087..28781.090 rows=25 loops=1)
      Sort Key: (CASE WHEN (items.sku IS NULL) THEN (items.title)::text ELSE pg_catalog.concat(items.title, ' (SKU: ', items.sku, ')') END)
      Sort Method: top-N heapsort  Memory: 30kB
      ->  HashAggregate  (cost=247677.77..247685.08 rows=585 width=118) (actual time=28779.658..28779.974 rows=664 loops=1)
          ->  Hash Right Join  (cost=2069.47..247645.64 rows=6425 width=118) (actual time=17798.898..28742.395 rows=60047 loops=1)
                Hash Cond: (details.applicable_id = items.id)
                ->  Seq Scan on details  (cost=0.00..220591.65 rows=6645404 width=8) (actual time=6.272..27702.717 rows=6646205 loops=1)
                      Filter: ((applicable_type = 'Listing'::citext) AND (status = 'Valid'::citext))
                      Rows Removed by Filter: 942
                ->  Hash  (cost=2062.16..2062.16 rows=585 width=118) (actual time=1.286..1.286 rows=664 loops=1)
                      Buckets: 1024  Batches: 1  Memory Usage: 90kB
                      ->  Bitmap Heap Scan on items  (cost=16.87..2062.16 rows=585 width=118) (actual time=0.157..0.748 rows=664 loops=1)
                            Recheck Cond: (user_id = 3)
                            ->  Bitmap Index Scan on index_items_on_user_id  (cost=0.00..16.73 rows=585 width=0) (actual time=0.141..0.141 rows=664 loops=1)
                                  Index Cond: (user_id = 3)

总运行时间:28781.238 毫秒

Answer 1

您是否有生成标题的表达式的索引？更好的是，一个关于 (user_id, title_expression)。

如果不是，那可能是一个很好的添加，以便在索引扫描的前 25 行中嵌套循环，看到 Postgres 无法合理地猜测哪 25 行随机(因此您当前正在进行 seq 扫描进入连接表)将需要。