建了索引却不走索引的原因 — 那些优化器对而你错的情况

-- 优化器的选择
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'completed';

Seq Scan on orders  (cost=0.00..20117.00 rows=612430 width=88)
                    (actual time=0.014..184.221 rows=611884 loops=1)
  Filter: (status = 'completed')
  Rows Removed by Filter: 188116
  Buffers: shared hit=1211 read=10906
Execution Time: 231.774 ms

-- 强制走索引的结果
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'completed';
RESET enable_seqscan;

Index Scan using idx_orders_status on orders
  (cost=0.42..68914.33 rows=612430 width=88)
  (actual time=0.061..1042.883 rows=611884 loops=1)
  Index Cond: (status = 'completed')
  Buffers: shared hit=8842 read=598112
Execution Time: 1094.201 ms

-- SSD 环境下更现实的值
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('created_at', 'user_id', 'status');

  attname   | n_distinct | correlation
------------+------------+-------------
 created_at |         -1 |    0.998304
 user_id    |      48210 |    0.003911
 status     |          6 |    0.412008

-- 用 INCLUDE 建覆盖索引 (PostgreSQL 11 以上)
CREATE INDEX CONCURRENTLY idx_orders_status_covering
  ON orders (status) INCLUDE (id, total, created_at);

Index Only Scan using idx_orders_status_covering on orders
  (actual time=0.038..142.118 rows=611884 loops=1)
  Index Cond: (status = 'completed')
  Heap Fetches: 1204

-- 不走索引: 列上套了函数
SELECT * FROM orders WHERE date(created_at) = '2026-07-26';

-- 走索引: 改写成范围条件,让列保持原样
SELECT * FROM orders
WHERE created_at >= '2026-07-26'
  AND created_at <  '2026-07-27';

-- 不走索引: 列这一侧有运算
SELECT * FROM order_items WHERE price * quantity > 100000;

-- 走索引: 移到生成列或表达式索引上
CREATE INDEX idx_items_amount ON order_items ((price * quantity));

CREATE INDEX idx_users_email_lower ON users (lower(email));

-- 走索引
SELECT * FROM users WHERE lower(email) = 'a@example.com';

-- 不走索引: 表达式不同
SELECT * FROM users WHERE lower(trim(email)) = 'a@example.com';

-- code 列是 varchar,索引也是按 varchar 建的
EXPLAIN SELECT * FROM products WHERE code = 12345;

ERROR:  operator does not exist: character varying = integer

-- id 是 bigint,而参数以 numeric 传进来的情况
EXPLAIN SELECT * FROM orders WHERE id = 1001::numeric;

Seq Scan on orders  (cost=0.00..24117.00 rows=4001 width=88)
  Filter: ((id)::numeric = '1001'::numeric)

-- 走索引: 前缀被固定,可以做索引范围查找
SELECT * FROM users WHERE name LIKE 'kim%';

-- 不走索引: 不知道起点,只能全部看一遍
SELECT * FROM users WHERE name LIKE '%kim%';

-- pg_trgm: 用索引处理子串搜索
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm
  ON users USING gin (name gin_trgm_ops);

EXPLAIN ANALYZE SELECT * FROM users WHERE name LIKE '%kim%';

Bitmap Heap Scan on users  (actual time=2.114..8.902 rows=412 loops=1)
  Recheck Cond: (name ~~ '%kim%'::text)
  ->  Bitmap Index Scan on idx_users_name_trgm  (actual time=1.884..1.884 rows=498 loops=1)
        Index Cond: (name ~~ '%kim%'::text)

CREATE INDEX idx_users_name_prefix
  ON users (name text_pattern_ops);

CREATE INDEX idx_orders_multi ON orders (user_id, status, created_at);

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'a@b.com' OR phone = '01012345678';

Bitmap Heap Scan on users  (actual time=0.061..0.064 rows=2 loops=1)
  Recheck Cond: ((email = 'a@b.com') OR (phone = '01012345678'))
  ->  BitmapOr  (actual time=0.052..0.052 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_users_email  (actual time=0.031..0.031 rows=1 loops=1)
        ->  Bitmap Index Scan on idx_users_phone  (actual time=0.019..0.019 rows=1 loops=1)

EXPLAIN ANALYZE
SELECT * FROM orders WHERE cancelled_at IS NULL;

Index Scan using idx_orders_cancelled_at on orders
  (actual time=0.022..1.884 rows=304 loops=1)
  Index Cond: (cancelled_at IS NULL)

-- 确认最后一次 ANALYZE 的时间以及此后的变更量
SELECT relname,
       n_live_tup,
       n_mod_since_analyze,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

 relname | n_live_tup | n_mod_since_analyze |    last_analyze     | last_autoanalyze
---------+------------+---------------------+---------------------+------------------
 orders  |     804112 |              611903 | 2026-07-19 03:12:44 | 2026-07-19 03:12:44

-- 大表按表级别调低阈值
ALTER TABLE orders SET (
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 5000
);

-- 批量导入结束后显式刷新
ANALYZE orders;

-- 只查询未处理订单的工作负载
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

-- 大小对比
SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders';

      indexrelname       |  size
-------------------------+---------
 idx_orders_status       | 42 MB
 idx_orders_pending      | 312 kB

SELECT s.relname AS table_name,
       s.indexrelname AS index_name,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

 table_name | index_name                 | idx_scan |  size
------------+----------------------------+----------+--------
 orders     | idx_orders_updated_at      |        0 | 88 MB
 events     | idx_events_legacy_type     |        0 | 41 MB

-- 确认统计收集的起始时间
SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

-- 如果只想把它排除出计划先观察一段时间而不删除 (直接修改目录,不建议在生产使用)
UPDATE pg_index SET indisvalid = false
WHERE indexrelid = 'idx_orders_updated_at'::regclass;

SELECT indexdef FROM pg_indexes WHERE indexname = 'idx_orders_updated_at';
DROP INDEX CONCURRENTLY idx_orders_updated_at;