PostgreSQL查询新增bigint列后执行时间翻倍原因与优化方案

我当前使用PostgreSQL 14.4版本，现有两张表：stocks_buys和stocks_quotes，其中stocks_quotes存储证券的每日收盘价格，stocks_buys存储具体股票交易/合约的总金额、持股数量等信息，两张表结构定义如下：

create_table "stocks_buys", force: :cascade do |t|
  t.bigint "security_id", null: false                                                                                                                         
  t.integer "total_cents", default: 0, null: false                                                                                                            
  t.decimal "quantity", precision: 8, scale: 2, default: "0.0"                                                                                                
  t.datetime "created_at", null: false                                                                                                                        
  t.datetime "updated_at", null: false                                                                                                                        
  t.date "date"
  t.bigint "account_id"                                                                                                                                      
  t.decimal "shares_not_sold", precision: 8, scale: 2, default: "0.0"                                                                                        
  t.index ["account_id"], name: "index_stocks_buys_on_account_id"                                                                                            
  t.index ["date"], name: "index_stocks_buys_on_date"                                                                                                        
  t.index ["security_id"], name: "index_stocks_buys_on_security_id"                                                                                          
end  

create_table "stocks_quotes", force: :cascade do |t|                                                                                                         
  t.bigint "security_id"                                                                                                                                     
  t.date "date"                                                                                                                                              
  t.datetime "created_at", default: -> { "CURRENT_TIMESTAMP" }, null: false                                                                                  
  t.datetime "updated_at", default: -> { "CURRENT_TIMESTAMP" }, null: false                                                                                  
  t.bigint "close_cents"                                                                                                                                           
  t.index ["date"], name: "index_stocks_quotes_on_date"                                                                                                      
  t.index ["security_id", "date"], name: "index_stocks_quotes_on_security_id_and_date", unique: true                                                         
  t.index ["security_id"], name: "index_stocks_quotes_on_security_id"                                                                                        
end 

业务需求是关联两张表，查询每笔买入合约从买入日期至今对应的所有报价数据，当前使用的查询SQL如下：

SELECT 
  "stocks_buys"."id" as "buy_id",                                                                                                                              
  "stocks_buys"."security_id",                                                                                                                                 
  "stocks_buys"."account_id",                                                                                                                                  
  "stocks_buys"."shares_not_sold" as "quantity",                                                                                                               
  "stocks_quotes"."date" as "date",                                                                                                                            
  "stocks_buys"."total_cents" as "cost_basis_total_cents",                                                                                                     
  "stocks_quotes"."close_cents",
  "stocks_buys"."quantity" as "cost_basis_quantity"                                                                                                            
FROM "stocks_buys"                                                                                                                                             
INNER JOIN "stocks_quotes" 
    on "stocks_quotes"."security_id" = "stocks_buys"."security_id"
   AND "stocks_quotes"."date" >= "stocks_buys"."date"                 
WHERE "stocks_buys"."shares_not_sold" > 0

当前数据规模约为4.3万条报价记录、430条买入记录，当SELECT列表包含close_cents列时，查询耗时约60-70ms，对应执行计划如下：

QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=2060.54..6186.27 rows=88844 width=48) (actual time=25.060..54.465 rows=29726 loops=1)
   Hash Cond: (stocks_buys.security_id = stocks_quotes.security_id)
   Join Filter: (stocks_quotes.date >= stocks_buys.date)
   Rows Removed by Join Filter: 344962
   Buffers: shared hit=1130
   ->  Seq Scan on stocks_buys  (cost=0.00..36.36 rows=115 width=40) (actual time=0.047..0.198 rows=115 loops=1)
         Filter: (shares_not_sold > '0'::numeric)
         Rows Removed by Filter: 314         Buffers: shared hit=31   ->  Hash  (cost=1526.35..1526.35 rows=42735 width=20) (actual time=24.983..24.984 rows=42735 loops=1)
         Buckets: 65536  Batches: 1  Memory Usage: 2850kB
         Buffers: shared hit=1099
         ->  Seq Scan on stocks_quotes  (cost=0.00..1526.35 rows=42735 width=20) (actual time=0.007..13.217 rows=42735 loops=1)
               Buffers: shared hit=1099
 Planning Time: 0.443 ms
 Execution Time: 56.382 ms
(16 rows)

Time: 57.790 ms

如果将SELECT列表中的close_cents列移除，查询耗时会降至原耗时的1/3，且执行计划完全不同，移除close_cents后的explain结果如下：

QUERY PLAN                                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.41..5527.21 rows=88302 width=40) (actual time=0.119..15.750 rows=29726 loops=1)
   Buffers: shared hit=568
   ->  Seq Scan on stocks_buys  (cost=0.00..36.36 rows=114 width=40) (actual time=0.067..0.355 rows=115 loops=1)
         Filter: (shares_not_sold > '0'::numeric)
         Rows Removed by Filter: 314
         Buffers: shared hit=31
   ->  Index Only Scan using index_stocks_quotes_on_security_id_and_date on stocks_quotes  (cost=0.41..36.30 rows=1187 width=12) (actual time=0.008..0.068 rows=258 loops=115)
         Index Cond: ((security_id = stocks_buys.security_id) AND (date >= stocks_buys.date))
         Heap Fetches: 0
         Buffers: shared hit=537
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.822 ms
 Execution Time: 18.706 ms
(14 rows)

执行SET enable_seqscan = OFF;强制PostgreSQL在包含close_cents列时走索引，数据库依然不会选择使用index_stocks_quotes_on_security_id_and_date索引，对应explain结果如下：

Merge Join  (cost=0.56..6958.49 rows=88912 width=48) (actual time=0.281..76.936 rows=29726 loops=1)
   Merge Cond: (stocks_buys.security_id = stocks_quotes.security_id)
   Join Filter: (stocks_quotes.date >= stocks_buys.date)
   Rows Removed by Join Filter: 344962
   Buffers: shared hit=732
   ->  Index Scan using index_stocks_buys_on_security_id on stocks_buys  (cost=0.27..148.00 rows=115 width=40) (actual time=0.180..0.427 rows=115 loops=1)         Filter: (shares_not_sold > '0'::numeric)
         Rows Removed by Filter: 314         Buffers: shared hit=78
   ->  Materialize  (cost=0.29..2249.14 rows=42735 width=20) (actual time=0.087..34.198 rows=383051 loops=1)
         Buffers: shared hit=654
         ->  Index Scan using index_stocks_quotes_on_security_id on stocks_quotes  (cost=0.29..2142.31 rows=42735 width=20) (actual time=0.076..9.197 rows=42735 loops=1)
               Buffers: shared hit=654
 Planning Time: 0.708 ms
 Execution Time: 78.437 ms

最初将close_cents字段定义为decimal类型（存储单位为分，仅为预留精度），后续改为bigint类型尝试优化性能，但没有明显效果。
核心疑问：close_cents列既没有参与JOIN关联条件，也没有参与WHERE过滤条件，为什么将其加入SELECT列表会导致查询执行时间大幅上升？有什么方法可以优化该查询的性能？

出现这个现象的核心原因是索引覆盖能力差异：

• 不查询close_cents时，现有联合索引index_stocks_quotes_on_security_id_and_date已经包含了查询需要的所有字段：security_id、date，PostgreSQL可以直接走Index Only Scan，不需要回表访问堆表数据，仅扫描索引就能拿到所有需要的列，IO成本极低，因此优化器选择了Nested Loop+索引扫描的高效执行计划。
• 把close_cents加入SELECT列表后，现有联合索引没有存储close_cents字段，无法继续走Index Only Scan。如果强制使用这个索引，每匹配到一条符合条件的记录，都需要根据索引中的元组指针回表查询堆表拿到close_cents的值，PostgreSQL优化器估算后认为，这种随机回表的IO成本比直接全表扫描做Hash Join更高，因此主动放弃了索引扫描方案，选择全表扫描+Hash Join的执行路径，耗时自然上升。
  强制关闭seqscan后依然没走到目标联合索引，也是因为优化器计算后发现，使用该索引需要大量随机回表，成本比走单字段security_id索引+Merge Join更高，因此没有选择Nested Loop方案。

最直接有效的优化方式是扩展现有联合索引，把close_cents作为包含列加入索引，打造覆盖索引，让查询可以直接走Index Only Scan不需要回表：

-- 先删除旧索引
DROP INDEX index_stocks_quotes_on_security_id_and_date;
-- 创建包含close_cents的覆盖索引：等值查询字段在前，范围查询字段在后，需要返回的非查询条件字段放INCLUDE列表
CREATE UNIQUE INDEX index_stocks_quotes_on_security_id_and_date 
ON stocks_quotes (security_id, date) INCLUDE (close_cents);

创建完这个索引后，即使SELECT包含close_cents，优化器也会选择和不带close_cents时一样的Nested Loop+Index Only Scan执行计划，耗时会降到15-20ms级别。

其他可选辅助优化：

• 定期对两张表执行ANALYZE命令更新统计信息，避免优化器因统计信息不准误判行数、选错执行计划
• 如果后续还需要从stocks_quotes查询其他字段，可按需加入INCLUDE列表，不要直接把非查询条件字段加到联合索引的排序键位置，避免索引过度膨胀降低写入性能
• 当前数据规模较小，60ms左右的耗时本身在可接受范围，但添加覆盖索引是长期最优方案，后续数据量增长到百万级也不会出现性能陡降

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