Postgresql 在索引应用时执行 seq_scan。为什么？

Question

我有一个在 varchar(24) 主键上进行连接的查询。这是一个关键的原因是遗留的并且有针对性地改变。但是，postgresql 查询规划器坚持进行顺序扫描，这对我来说似乎不合理。我用“SET enable_seqscan = off”将此查询加速了 8 倍这一事实来支持我的“不合理”主张。

我运行了“真空分析”；我玩过统计设置，但到目前为止运气不好。

查询是

select inventry.id, inventry.count, sum(invenwh.count) 
from invenwh join inventry on inventry.id=invenwh.id
where inventry.product_c='CAT17' 
group by 1, 2;

以下设置运行此查询的数据库。

drop table if exists inventry;
drop table if exists inwh;
drop table if exists invenwh;
drop table if exists inprodcategory;

-- Create 50 product categories.
create table inprodcategory as 
select i as id, concat('CAT', lpad(i::text, 2, '0'))::varchar(10) as category
from generate_series(1, 50, 1) as i;

-- Create 245,000 inventory items
create table inventry as 
select 
    concat('ITEM', lpad(i::text, 6, '0'))::varchar(24) as id, 
    concat('Item #', i::text)::varchar(50) as descr_1,
    c.category as product_c,
    (case when random() < 0.05 then (random()*70)::int else 0::int end) as count
from generate_series(1, 245000, 1) as i
    join inprodcategory as c on c.id=(i%50)::int;

-- Create 70 warehouses
create table inwh as 
select concat('WAREHOUSE', lpad(i::text, 2, '0'))::varchar(10) as warehouse
from generate_series(1, 70, 1) as i;

-- Create (ugly) cross-join table with counts/warehouse
create table invenwh as 
select id, warehouse, 
    (case when random() < 0.05 then (random()*10)::int else 0::int end) as count
from inventry, inwh;

create index on invenwh (id);
create index on inventry (id);

运行上面的之后，就可以运行查询了。在我的带有 SSD、i7 和 16gb 内存的硬件上，它需要 4 秒，但如果我运行“set enable_seqscan=off”，它需要大约 500 毫秒。

编辑:添加 explain(analyze, buffers)

HashAggregate  (cost=449773.25..449822.25 rows=4900 width=19) (actual time=4180.006..4181.092 rows=4900 loops=1)
  Group Key: inventry.id, inventry.count
  Buffers: shared hit=4526 read=121051
  ->  Hash Join  (cost=5058.50..447200.75 rows=343000 width=19) (actual time=1285.800..4086.398 rows=343000 loops=1)
        Hash Cond: ((invenwh.id)::text = (inventry.id)::text)
        Buffers: shared hit=4526 read=121051
        ->  Seq Scan on invenwh  (cost=0.00..291651.00 rows=16807000 width=15) (actual time=0.077..1949.843 rows=16807000 loops=1)
              Buffers: shared hit=2530 read=121051
        ->  Hash  (cost=4997.25..4997.25 rows=4900 width=15) (actual time=48.897..48.897 rows=4900 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 230kB
              Buffers: shared hit=1996
              ->  Seq Scan on inventry  (cost=0.00..4997.25 rows=4900 width=15) (actual time=21.903..47.031 rows=4900 loops=1)
                    Filter: ((product_c)::text = 'CAT17'::text)
                    Rows Removed by Filter: 235200
                    Buffers: shared hit=1996
Planning time: 4.266 ms
Execution time: 4181.395 ms

编辑:具体的后续问题

感谢@a_horse_with_no_name(非常感谢!!)看来降低 random_page_cost 是可行的。这似乎或多或少与 https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server 一致。

问:我可以运行任何基准来发现 random_page_cost 的最优值吗？在生产环境中，我使用的是 SCSI 磁盘 (LSI MR9260-8i)。

问:我觉得统计数据也可能与此相关，但我在互联网上的 pg-stats-for-dummies 类型页面上空空如也。学习统计数据有什么提示吗？

Answer 1

当规划器估计的成本与实际执行时间不匹配时，应调整成本设置以更好地匹配您的硬件。

各种旋钮记录在 Planner Cost Constants .

特别是关于 random_page_cost 的建议与您的情况相关:

Storage that has a low random read cost relative to sequential, e.g. solid-state drives, might also be better modeled with a lower value for random_page_cost.

另见 Random Page Cost Revisited使用 5 种不同的存储类型获取有关此参数的更多调整建议。

TL;DR:对于 SSD，首先尝试 1.5 以获得 random_page_cost。