PostgreSQL多列（等值+范围）查询慢，如何创建索引优化？

表结构 #

id       | tx_hash | tx_status | created_at
----------+---------+-----------+---------------------------
15000000  | 0x0011  | 0         | 2021-07-30 06:42:00.267694
15000001  | 0x0011  | 0         | 2021-07-30 06:42:00.267694
15000002  | 0x0011  | 0         | 2021-07-30 06:42:00.267694
...
16000000  | 0x0011  | 0         | 2021-07-30 06:42:00.267694

原查询语句 #

select id, tx_hash, tx_status, created_at 
from pool_transaction_entries 
where tx_status = 0 and id > 15006000 
order by id desc 
limit 1;

执行计划分析 #

postgres=> explain analyze verbose select id, tx_hash, tx_status, created_at from pool_transaction_entries where tx_status = 0 and id > 15006000 order by id  desc limit 1;
                                                                                        QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..133.80 rows=1 width=87) (actual time=21415.241..21415.242 rows=0 loops=1)
   Output: id, tx_hash, tx_status, created_at
   ->  Index Scan Backward using pool_transaction_entries_pkey on public.pool_transaction_entries  (cost=0.43..3868.12 rows=29 width=87) (actual time=21415.238..21415.239 rows=0 loops=1)
         Output: id, tx_hash, tx_status, created_at
         Index Cond: (pool_transaction_entries.id > 15006000)
         Filter: (pool_transaction_entries.tx_status = 0)
         Rows Removed by Filter: 3556
 Query Identifier: 3330758434230110582
 Planning Time: 54.206 ms
 Execution Time: 21415.281 ms
(10 rows)

从执行计划可见，当前查询依赖主键索引反向扫描，先筛选id > 15006000的记录，再过滤tx_status = 0，总共扫描并丢弃了3556行数据才返回结果，这是执行速度慢的核心原因。

优化方案 #

1. 创建复合B树索引（核心优化） #

针对查询的等值条件tx_status = 0、范围条件id > 15006000，以及order by id desc的排序需求，创建复合索引：

CREATE INDEX idx_pool_tx_status_id ON pool_transaction_entries (tx_status, id DESC);

该索引会先按tx_status分组，分组内的记录按id降序排列。查询时可直接定位到tx_status = 0的分组，快速找到第一个满足id > 15006000的记录（即符合条件的最大id），无需扫描大量无关行。

2. 创建覆盖索引（消除回表开销） #

若想彻底避免查询时访问原表数据，可使用PostgreSQL的INCLUDE子句创建覆盖索引，将查询所需的所有列包含进来：

CREATE INDEX idx_pool_tx_status_id_include ON pool_transaction_entries (tx_status, id DESC) INCLUDE (tx_hash, created_at);

此索引本身包含了id、tx_status、tx_hash、created_at所有查询列，查询时直接从索引取数，无需回表，性能会进一步提升。

3. 更新表统计信息（辅助优化） #

若表数据有较大变动，可能导致优化器统计信息不准确，执行以下命令更新统计，确保优化器能正确选择最优索引：

ANALYZE pool_transaction_entries;

创建索引后，重新执行原查询并查看EXPLAIN ANALYZE结果，会看到查询使用新索引，执行时间将大幅降低。

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