为何关联transaction表时采用Seq Scan而非Index Scan？

我有两张需执行全文检索的表：transaction表和transaction_line表。检索始终通过transaction_line表的日期范围进行过滤，随后希望利用主键索引将过滤后的transaction_line记录与transaction表关联，再对关联结果执行全文检索。但当前查询在关联transaction表时使用了Sequential Scan（全表扫描）而非Index Scan（索引扫描）。

给定查询语句 #

explain analyse with
    t as (select transaction.id, transaction.document as t_doc, transaction_line.document as tl_doc                          from transaction_line
left join transaction on transaction_id = transaction.id
where '2023-12-30' <= date and date <= '2023-12-31')
select id from t where t.t_doc @@ to_tsquery('norwegian', '2400') or
t.tl_doc @@ to_tsquery('norwegian', '2400');

当前查询执行计划 #

+-------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                     |
+-------------------------------------------------------------------------------------------------------------------------------+
|Hash Left Join  (cost=1301.31..2576.17 rows=36 width=8) (actual time=26.248..28.903 rows=23 loops=1)                           |
|  Hash Cond: (transaction_line.transaction_id = transaction.id)                                                                |
|  Filter: ((transaction.document @@ '''2400'''::tsquery) OR (transaction_line.document @@ '''2400'''::tsquery))                |
|  Rows Removed by Filter: 305                                                                                                  |
|  ->  Bitmap Heap Scan on transaction_line  (cost=12.79..1286.50 rows=439 width=55) (actual time=0.193..2.598 rows=328 loops=1)|
|        Recheck Cond: (('2023-12-30'::date <= date) AND (date <= '2023-12-31'::date))                                          |
|        Heap Blocks: exact=93                                                                                                  |
|        ->  Bitmap Index Scan on date_idx  (cost=0.00..12.68 rows=439 width=0) (actual time=0.135..0.135 rows=328 loops=1)     |
|              Index Cond: ((date >= '2023-12-30'::date) AND (date <= '2023-12-31'::date))                                      |
|  ->  Hash  (cost=1101.01..1101.01 rows=15001 width=63) (actual time=25.853..25.854 rows=15001 loops=1)                        |
|        Buckets: 16384  Batches: 1  Memory Usage: 1531kB                                                                       |
|        ->  Seq Scan on transaction  (cost=0.00..1101.01 rows=15001 width=63) (actual time=0.025..19.925 rows=15001 loops=1)   |
|Planning Time: 5.917 ms                                                                                                        |
|Execution Time: 30.389 ms                                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------+

预期查询执行计划 #

+-------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------+
|Nested Loop Left Join  (cost=13.07..3293.88 rows=36 width=8) (actual time=0.399..6.457 rows=23 loops=1)                              |
|  Filter: ((transaction.document @@ '''2400'''::tsquery) OR (transaction_line.document @@ '''2400'''::tsquery))                      |
|  Rows Removed by Filter: 305                                                                                                        |
|  ->  Bitmap Heap Scan on transaction_line  (cost=12.79..1286.50 rows=439 width=55) (actual time=0.145..2.198 rows=328 loops=1)      |
|        Recheck Cond: (('2023-12-30'::date <= date) AND (date <= '2023-12-31'::date))                                                |
|        Heap Blocks: exact=93                                                                                                        |
|        ->  Bitmap Index Scan on date_idx  (cost=0.00..12.68 rows=439 width=0) (actual time=0.094..0.094 rows=328 loops=1)           |
|              Index Cond: ((date >= '2023-12-30'::date) AND (date <= '2023-12-31'::date))                                            |
|  ->  Index Scan using transaction_pkey on transaction  (cost=0.29..4.56 rows=1 width=63) (actual time=0.012..0.012 rows=1 loops=328)|
|        Index Cond: (id = transaction_line.transaction_id)                                                                           |
|Planning Time: 1.905 ms                                                                                                              |
|Execution Time: 6.625 ms                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------+

补充说明 #

补充说明（09.11.23-10:20）：
目前暂无生产数据，现有15001条transaction记录，预计每个schema每年会产生约1000万条transaction记录，以及2000-3000万条transaction_line记录。由于需生成无间隙序列数据，生成这些量级数据耗时较长。因此，为找到不足10%的transaction记录而执行千万级Seq Scan，成本过高。

核心原因：优化器的成本估算选择了哈希连接 #

PostgreSQL查询优化器会基于数据量、统计信息和内置成本模型选择连接策略：

• 当前测试数据中transaction表仅15001条记录，优化器认为全表扫描后构建哈希表的总成本（1101.01）比328次索引扫描+嵌套循环的总成本（估算3293.88）更低，因此选择了Hash Left Join + 全表扫描的方案。
• 优化器默认认为内存哈希操作的效率高于多次索引查找，尤其是小表场景下。

引导优化器使用嵌套循环+索引扫描的方法 #

1. 调整查询结构，提前过滤数据 #

将全文检索条件提前到CTE内部，减少后续连接的数据量，让优化器更倾向于嵌套循环：

explain analyse with
    filtered_lines as (
        -- 先筛选transaction_line中命中全文检索的记录
        select transaction_id, document as tl_doc 
        from transaction_line
        where date between '2023-12-30' and '2023-12-31'
          and document @@ to_tsquery('norwegian', '2400')
        union all
        -- 再筛选需要关联transaction表做检索的记录
        select transaction_id, null as tl_doc
        from transaction_line
        where date between '2023-12-30' and '2023-12-31'
          and not document @@ to_tsquery('norwegian', '2400')
    )
select t.id
from filtered_lines fl
left join transaction t on fl.transaction_id = t.id
where fl.tl_doc is not null or t.document @@ to_tsquery('norwegian', '2400');

2. 临时调整优化器参数（会话级别） #

通过参数降低哈希连接优先级，或提升索引扫描的竞争力：

-- 临时禁用哈希连接，仅当前会话生效
set enable_hashjoin = off;

-- 或降低随机页读取成本（默认4），让索引扫描成本更具优势
set random_page_cost = 1.1;

注意：参数调整仅建议用于测试，生产环境需结合实际数据量评估。

3. 更新统计信息 #

确保优化器掌握准确的数据分布：

analyse transaction;
analyse transaction_line;

4. 生产量级数据的自动优化 #

当transaction表达到千万级规模时，全表扫描的成本会急剧上升，此时328次索引扫描的总成本会远低于全表扫描，优化器会自动选择嵌套循环+索引扫描的方案，无需手动干预。

内容的提问来源于stack exchange，提问作者regine.urtegard