相同查询与索引下PostgreSQL性能差异优化及原因咨询

Bitmap Heap Scan on shipments (cost=1996.94..228726.45 rows=59403 width=1325) (actual time=143.089..4822.412 rows=38685 loops=1)
Recheck Cond: (buyer_id = 198917)
Heap Blocks: exact=22308
-> Bitmap Index Scan on index_shipments_on_buyer_id (cost=0.00..1982.09 rows=59403 width=0) (actual time=136.081..136.081 rows=38685 loops=1)
Index Cond: (buyer_id = 198917)
Planning time: 0.176 ms
Execution time: 4831.741 ms
(7 rows)

db=# EXPLAIN ANALYZE SELECT "shipments".* FROM "shipments" WHERE "shipments"."supplier_id" = 198917;
QUERY PLAN #

Index Scan using index_shipments_on_supplier_id on shipments (cost=0.57..7227.48 rows=1840 width=1325) (actual time=0.055..0.055 rows=0 loops=1)
Index Cond: (supplier_id = 198917)
Planning time: 0.203 ms
Execution time: 0.090 ms
(4 rows)

### 问题
1. 如何优化第一个查询使其性能与第二个查询持平？
2. 为何PostgreSQL在看似相同的场景下采用完全不同的查询策略，导致性能差异如此巨大？

---

# 解答

## 1. 第一个查询的优化方案
- **创建覆盖索引**：如果不需要查询所有字段，仅选择业务必需的字段，创建包含这些字段的覆盖索引，让数据库直接从索引读取数据，无需回表扫描堆数据：
  ```sql
  CREATE INDEX idx_shipments_buyer_include ON shipments (buyer_id) INCLUDE (字段1, 字段2, ...); -- 替换为实际需要的字段

若必须查询全部字段，此方案不适用。

• 引导优化器使用索引扫描：临时关闭位图扫描（仅当前会话有效），或通过排序、查询提示引导优化器选择索引扫描：

  -- 临时关闭位图扫描
  SET enable_bitmapscan = off;
  SELECT * FROM shipments WHERE buyer_id = 198917;
  
  -- 或通过排序引导优化器选择索引扫描
  SELECT * FROM shipments WHERE buyer_id = 198917 ORDER BY buyer_id;
  
  -- PostgreSQL 12+支持查询提示
  SELECT * FROM shipments WHERE buyer_id = 198917 /*+ IndexScan(shipments index_shipments_on_buyer_id) */;
  

  注意强制指定执行策略需确保数据分布长期稳定，避免产生反效果。
• 更新统计信息：如果表数据有大量新增、删除或更新，执行ANALYZE shipments;让数据库获取最新的 data 分布统计，优化器可能会调整执行计划。
• 表分区：若shipments表数据量极大，可按buyer_id进行分区，缩小单次查询需要扫描的数据范围。

2. 执行策略差异的原因 #

PostgreSQL的查询优化器会基于数据分布统计和成本估算选择最优执行计划，两个查询的核心差异点：

• 匹配行数占比：第一个查询实际返回38685行，占表的比例较高；第二个查询实际返回0行，占比极低。当匹配行数超过表总量的5%-10%时，优化器认为位图堆扫描更高效：
  • 索引扫描需要逐个通过索引条目回表取数据，行数较多时，多次随机IO的总成本会很高；
  • 位图堆扫描先通过索引构建匹配行的位图，再批量扫描堆数据，减少随机IO的次数，更适合批量数据读取。
• 数据分布差异：buyer_id=198917的记录在表中占比很高，而supplier_id=198917的记录极少甚至不存在。优化器通过统计信息知晓这一分布情况，因此选择了不同的执行策略。
• IO成本估算：优化器会计算两种扫描方式的IO成本，当批量读取的成本低于多次随机回表的成本时，就会选择位图堆扫描。

内容的提问来源于stack exchange，提问作者sscirrus