PHP小码农

Appearance

effective_cache_size ​

effective_cache_size 是 PostgreSQL 中最容易被误解的参数：它不分配任何内存，只是一个告诉优化器「这台机器上大概有多少可用的文件系统缓存」的估算值。它不占一个字节，却直接决定了索引扫描的代价估算，进而影响优化器是选索引还是选全表扫描。设错了会让优化器系统性误判，是「明明有索引却走全表扫」这类问题的首要嫌疑犯。

前置知识

阅读本节前，建议先了解：shared_buffers 共享缓冲区

基础概念 ​

它是什么 ​

项目说明
参数名effective_cache_size
默认值4GB
单位8kB 块（配置时可用 MB/GB）
上下文user（普通用户可 SET，reload 生效）
是否分配内存否（纯估算值）
作用对象优化器的代价估算（cost estimation）
影响范围索引扫描 / 全表扫描的选择
sql
SELECT name, setting, unit, context, boot_val,
       pg_size_pretty((setting::bigint * 8192)::bigint) AS default_size
FROM pg_settings WHERE name = 'effective_cache_size';
text
         name          | setting | unit | context | boot_val | default_size
-----------------------+---------+------+---------+----------+--------------
 effective_cache_size  | 524288  | 8kB  | user    | 524288   | 4096 MB
(1 row)

默认 524288 × 8kB = 4GB。

最常见的误解

❌ 错误理解：effective_cache_size 会分配 4GB 内存给 PostgreSQL
✅ 正确理解：它只是告诉优化器「系统大约有 4GB 缓存可用」

想验证这一点，可以把它调成 1TB 然后看内存占用——不会有任何变化：

sql
SET effective_cache_size = '1TB';
SELECT pg_size_pretty(setting::bigint * 8192) FROM pg_settings WHERE name = 'effective_cache_size';
-- 1099511627776 MB —— 但 RSS 内存占用没变

它到底代表什么 ​

effective_cache_size 的官方定义是：对「shared_buffers + 操作系统页缓存」中可被用于缓存数据文件的部分的估计。

text
┌─────────────────────────────────────────────────────────────┐
│            effective_cache_size 的语义                      │
│                                                             │
│  ┌─────────────────────┐  ┌──────────────────────────────┐ │
│  │  shared_buffers     │  │  OS 页缓存（可缓存数据文件的部分）│ │
│  │  （PostgreSQL 精确  │  │  （内核管理，数量动态变化）      │ │
│  │   控制的部分）      │  │                              │ │
│  └─────────────────────┘  └──────────────────────────────┘ │
│                                                             │
│         effective_cache_size ≈ 两者之和                      │
└─────────────────────────────────────────────────────────────┘
                    │
                    ▼
        优化器用它估算「索引扫描时命中缓存的比例」

它如何影响执行计划 ​

优化器在评估「索引扫描」时会考虑：需要读 N 个页，其中多少可能已在缓存里？ 这个「缓存命中率」就是由 effective_cache_size 与表的总大小之比推出来的。

text
表大小 << effective_cache_size
    │
    ▼
优化器认为：几乎所有页都在缓存里
    │
    ▼
索引扫描的代价估算大幅下降（随机读变「廉价」）
    │
    ▼
更倾向选索引扫描

─────────────────────────────────────────────

表大小 >> effective_cache_size
    │
    ▼
优化器认为：大部分页需要真实磁盘读
    │
    ▼
索引扫描代价上升
    │
    ▼
更倾向选全表扫描（顺序读）

官方文档中「索引扫描代价」的近似公式体现了两者的关系：

text
索引扫描成本 ≈ random_page_cost × (索引页数 + 需要回表的数据页数 × 缓存未命中率)

而缓存未命中率是由 effective_cache_size 推导的：effective_cache_size 越大，优化器认为缓存未命中率越低，索引扫描就越「便宜」。

与相关参数的关系 ​

参数默认值是否分配内存作用
effective_cache_size4GB❌ 否优化器估算缓存可用量
shared_buffers128MB✅ 是真实的 PostgreSQL 页缓存
random_page_cost4.0❌ 否随机页读取代价
seq_page_cost1.0❌ 否顺序页读取代价
cpu_tuple_cost0.01❌ 否处理每行的 CPU 代价
cpu_index_tuple_cost0.005❌ 否处理每个索引项的代价
cpu_operator_cost0.0025❌ 否每次操作符/函数的代价

effective_cache_size 与 random_page_cost 必须配套调整

在 SSD 上，两者都要动：

  • random_page_cost 从 4.0 降到 1.0~1.5（SSD 随机读几乎与顺序读一样快）。
  • effective_cache_size 设为内存的 50%~75%。

只改其中一个可能导致优化器行为异常。 例如 effective_cache_size 设得极大而 random_page_cost 保持 4.0，会让优化器过度偏好索引（因为它以为缓存几乎是无限的）。

设置的经验法则 ​

场景建议值说明
专用数据库服务器内存的 50%~75%最常见
与 Web 服务混部内存的 25%~50%给应用留内存
内存较小的容器容器限额的 50%~75%按 cgroup 限额算
明确的工作集很小可设为大于总内存让优化器认为「全在缓存」
明确工作集远大于内存保守设定避免高估缓存

语法与用法 ​

配置写法 ​

ini
# postgresql.conf —— 64GB 内存的专用数据库服务器
effective_cache_size = '48GB'      # 75%

# 32GB 内存，与 Web 混部
effective_cache_size = '16GB'      # 50%

# 8GB 容器限额
effective_cache_size = '6GB'       # 75%

修改与生效 ​

sql
-- 全局修改（reload 生效，不影响已有会话的参数快照？—— 注意它是 user 上下文）
ALTER SYSTEM SET effective_cache_size = '48GB';
text
ALTER SYSTEM
sql
-- 核验
SELECT name, setting, unit, reset_val, source, sourcefile
FROM pg_settings WHERE name = 'effective_cache_size';
text
         name          | setting | unit | reset_val |       source       |                   sourcefile
-----------------------+---------+------+-----------+--------------------+-------------------------------------------------
 effective_cache_size  | 6291456 | 8kB  | 6291456   | configuration file | /var/lib/postgresql/18/main/postgresql.auto.conf
(1 row)

6291456 × 8kB = 48GB。

sql
-- 会话级（只影响当前会话的代价估算）
SET effective_cache_size = '96GB';
text
SET
bash
# 或直接编辑文件后 reload
pg_ctl reload -D /var/lib/postgresql/18/main

effective_cache_size 的生效很「即时」

虽然它是 user 上下文（理论上会话可设），但它的作用是影响后续查询的计划生成。也就是说：

  • 配置改动 + reload 后，新生成的计划立刻使用新值（即使是已存在的连接）。
  • 已有连接如果不 RESET，其 SHOW 值可能仍是旧值，但计划生成时用的是 reset_val 所对应的一致性视图。

调优时为了确认效果，最可靠的方式是在会话里直接 SET 然后做 EXPLAIN：

sql
SET effective_cache_size = '48GB';
EXPLAIN SELECT ...;

验证对计划的影响 ​

sql
-- 先看默认值下计划
RESET effective_cache_size;
EXPLAIN (COSTS) SELECT * FROM orders WHERE customer_id = 12345;
text
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using idx_orders_customer on orders  (cost=0.43..1842.11 rows=412 width=120)
(1 row)
sql
-- 改成极小值，观察计划变化
SET effective_cache_size = '128MB';
EXPLAIN (COSTS) SELECT * FROM orders WHERE customer_id = 12345;
text
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..48219.33 rows=412 width=120)
   Filter: (customer_id = 12345)
(1 row)
sql
-- 改成合理的大值
SET effective_cache_size = '48GB';
EXPLAIN (COSTS) SELECT * FROM orders WHERE customer_id = 12345;
text
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using idx_orders_customer on orders  (cost=0.43..204.11 rows=412 width=120)
(1 row)

三次的计划与代价都不同，这就是 effective_cache_size 的全部作用。

详细说明 ​

一、索引扫描代价的估算逻辑 ​

用官方文档（Using EXPLAIN）给出的例子来理解。假设一张表有 10000 行、345 个数据页：

text
顺序扫描代价 = seq_page_cost × 页数 + cpu_tuple_cost × 行数
             = 1.0 × 345 + 0.01 × 10000
             = 345 + 100
             = 445

索引扫描的成本则要复杂得多——它需要估算：

text
索引扫描代价 = random_page_cost × 索引页数
             + random_page_cost × 有效缓存未命中率 × 数据页数
             + cpu_index_tuple_cost × 索引项数
             + cpu_tuple_cost × 命中行数
             + cpu_operator_cost × 条件数 × 行数

其中「有效缓存未命中率」正是由 effective_cache_size 与表大小之比得到的。所以：

  • effective_cache_size 越大 → 未命中率越低 → 索引扫描越便宜。
  • effective_cache_size 越小 → 未命中率越高 → 索引扫描越贵，容易退化为全表扫描。

用 EXPLAIN 观察实际代价变化：

sql
-- 对比不同 effective_cache_size 下的索引扫描代价
SET effective_cache_size = '128MB';
EXPLAIN (COSTS) SELECT count(*) FROM orders WHERE created_at > '2026-01-01';
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate  (cost=218342.11..218342.12 rows=1 width=8)
   ->  Index Scan using idx_orders_created_at on orders  (cost=0.43..192113.44 rows=1048512 width=0)
         Index Cond: (created_at > '2026-01-01 00:00:00'::timestamp without time zone)
(2 rows)
sql
SET effective_cache_size = '48GB';
EXPLAIN (COSTS) SELECT count(*) FROM orders WHERE created_at > '2026-01-01';
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate  (cost=118342.11..118342.12 rows=1 width=8)
   ->  Index Scan using idx_orders_created_at on orders  (cost=0.43..92113.44 rows=1048512 width=0)
         Index Cond: (created_at > '2026-01-01 00:00:00'::timestamp without time zone)
(2 rows)

代价从 218342 降到 118342，降低了约 46%。这就是把「缓存命中率」的假设从悲观改为乐观的效果。

二、如何估算合理的值 ​

方法一：按内存比例（最快）。

text
effective_cache_size = 总内存 × 75%
bash
# 先看系统内存
free -g
text
               total        used        free      shared  buff/cache   available
Mem:              62          19           2           8            41          38
Swap:              2           0           2
ini
# 62GB 内存，专用数据库服务器
effective_cache_size = '46GB'

方法二：按实际 OS 缓存观察值（最准确）。

在业务高峰时观察 free：

bash
free -b
text
               total        used        free      shared  buff/cache   available
Mem:     66571993088 20401094656 2147483648 8697309696 43903909888 40706787328
Swap:     2147483648           0  2147483648

计算：

text
buff/cache ≈ 41GB
shared     ≈ 8GB（这部分是 shared_buffers，已含在 buff/cache 里）

effective_cache_size ≈ buff/cache 中的文件缓存部分 ≈ 41GB - 8GB(共享内存) ≈ 33GB

保守取值：

ini
effective_cache_size = '32GB'

方法三：用 pg_statio 观察实际的缓存效率。

sql
SELECT sum(heap_blks_read) AS disk_reads,
       sum(heap_blks_hit)  AS cache_hits,
       round(sum(heap_blks_hit) * 100.0 /
             nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS hit_pct
FROM pg_statio_user_tables;
text
 disk_reads | cache_hits | hit_pct
------------+------------+---------
    1928431 | 3812093211 |   99.95

99.95% 说明绝大多数读都能被缓存满足（shared_buffers + OS 缓存），支持把 effective_cache_size 设得较高。

这个命中率包含 shared_buffers 与 OS 缓存两层

heap_blks_hit 是 shared_buffers 命中；heap_blks_read 是「没在 shared_buffers 里找到、需要向 OS 请求」的次数——但这次 OS 请求可能被 OS 页缓存满足，仍然是快的。

所以命中率高支持较大的 effective_cache_size，但不能直接推出确切值。它的实际用途是「确认缓存确实在起作用，从而有信心把 effective_cache_size 设高」。

三、设得过大与过小的后果 ​

设置优化器的判断后果
过大以为几乎所有数据都在缓存，索引扫描极便宜过度偏好索引扫描；对冷数据/大范围扫描会产生大量随机 I/O
过小以为缓存很少，索引扫描很贵过度偏好顺序扫描；即使有很好的索引也不用，白白多读数据
合理与真实内存布局相符计划选择稳定、符合实际

具体症状对照：

text
effective_cache_size 设得过小（如设成 128MB）的典型症状：
├── 有索引的等值查询仍走 Seq Scan
├── ORDER BY ... LIMIT 1 没走索引
├── 小范围的 BETWEEN 查询走全表扫
└── EXPLAIN 里 Filter 而非 Index Cond

effective_cache_size 设得过大（如设成 4TB）的典型症状：
├── 优化器选了索引扫描，但实际每次要读几十万页随机 I/O
├── 大范围范围查询变慢（随机读比顺序读慢得多）
├── bitmap heap scan 被优先选为 index scan 的替代
└── 慢查询日志里大量 "Index Scan" 但耗时很长

四、用 EXPLAIN 做参数敏感性实验 ​

一个完整的实验流程，量化 effective_cache_size 对计划的影响：

sql
-- 建立基线
RESET ALL;
SELECT name, setting, unit FROM pg_settings WHERE name IN ('effective_cache_size', 'random_page_cost');
text
         name          | setting | unit
-----------------------+---------+------
 effective_cache_size  | 524288  | 8kB
 random_page_cost      | 4       |
(2 rows)
sql
-- 定义一个要观察的查询
EXPLAIN (COSTS, FORMAT TEXT)
SELECT c.name, count(*) AS cnt
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01'
GROUP BY c.name
ORDER BY cnt DESC
LIMIT 20;
text
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Limit  (cost=218342.11..218342.16 rows=20 width=40)
   ->  Sort  (cost=218342.11..218342.55 rows=176 width=40)
         Sort Key: (count(*)) DESC
         ->  HashAggregate  (cost=218336.11..218338.31 rows=176 width=40)
               Group Key: c.name
               ->  Hash Join  (cost=12410.22..217128.44 rows=402144 width=16)
                     Hash Cond: (o.customer_id = c.id)
                     ->  Seq Scan on orders o  (cost=0.00..192113.11 rows=1048512 width=12)
                           Filter: (created_at >= '2026-01-01 00:00:00'::timestamp without time zone)
                     ->  Hash  (cost=7621.11..7621.11 rows=387110 width=16)
                           ->  Seq Scan on customers c  (cost=0.00..7621.11 rows=387110 width=16)
(10 rows)
sql
-- 逐档调整，观察代价变化
SET effective_cache_size = '1GB';
EXPLAIN (COSTS) SELECT ... ;
-- Limit (cost=218342.11..218342.16 ...)
sql
SET effective_cache_size = '16GB';
EXPLAIN (COSTS) SELECT ... ;
-- Limit (cost=168342.11..168342.16 ...)
sql
SET effective_cache_size = '64GB';
EXPLAIN (COSTS) SELECT ... ;
-- Limit (cost=118342.11..118342.16 ...)
sql
SET effective_cache_size = '512GB';
EXPLAIN (COSTS) SELECT ... ;
-- Limit (cost=98112.11..98112.16 ...)
-- 注意：代价不再线性下降，说明已趋于饱和

找到「代价曲线拐点」

把 effective_cache_size 从 1GB 到 1TB 逐档试，若某档之后计划不再变化、代价趋于饱和，说明继续调大已无意义。把值设在这个拐点稍上方是稳妥的选择。

五、容器环境的正确算法 ​

容器里必须按 cgroup 限额算，并且要注意容器看到的内存可能是宿主机的：

bash
# cgroup v2
cat /sys/fs/cgroup/memory.max
# 8589934592   → 8GB

# cgroup v1
cat /sys/fs/cgroup/memory/memory.limit_in_bytes
bash
# 容器内 free 可能显示宿主机内存，不要被误导
free -h
text
               total        used        free      shared  buff/cache   available
Mem:           251Gi        ...         ...        ...          ...         ...

251Gi 是宿主机内存，不是容器限额。按它配 effective_cache_size 会导致严重误判。

ini
# 正确：按 8GB 限额算
effective_cache_size = '6GB'

Kubernetes 里可以用环境变量注入：

yaml
env:
  - name: PG_EFFECTIVE_CACHE_SIZE
    value: "6GB"
ini
# postgresql.conf
effective_cache_size = '6GB'

六、与工作集大小的关系 ​

对 effective_cache_size 最精准的设定方式是知道工作集有多大。

sql
-- 估算表与索引的总大小（工作集的上界）
SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid))       AS table_size,
       pg_size_pretty(pg_indexes_size(relid))        AS index_size
FROM pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
text
 schemaname |   relname    | total_size | table_size | index_size
------------+--------------+------------+------------+------------
 public     | orders       | 24 GB      | 16 GB      | 8192 MB
 public     | order_items  | 12 GB      | 8 GB       | 4096 MB
 public     | customers    | 3072 MB    | 2048 MB    | 1024 MB
 public     | products     | 1024 MB    | 768 MB     | 256 MB
 public     | sessions     | 512 MB     | 384 MB     | 128 MB
(5 rows)
sql
-- 数据库总大小
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database WHERE datname IS NOT NULL ORDER BY pg_database_size(datname) DESC;
text
  datname  |  size
-----------+---------
 myapp     | 46 GB
 analytics | 128 GB
 postgres  | 7997 kB
(2 rows)

判断逻辑：

场景建议
热点工作集（如前 5 张表）< 内存effective_cache_size 可设为「热点工作集 × 1.5 ~ 2」
工作集 ≈ 内存设为内存的 75%
工作集 >> 内存设为内存的 75%（不要更高，避免高估）

七、只对特定会话/查询调优 ​

effective_cache_size 是 user 上下文，可以在会话内或事务内临时调整，用于验证计划：

sql
-- 只在本次事务内生效，用于验证「调大后计划是否更好」
BEGIN;
SET LOCAL effective_cache_size = '96GB';
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
COMMIT;

也可以为特定角色设置：

sql
-- 报表角色：假设报表库有更多缓存可用，或反之
ALTER ROLE report_ro SET effective_cache_size = '32GB';
sql
SELECT rolname, rolconfig FROM pg_roles WHERE rolconfig IS NOT NULL;
text
   rolname   |                rolconfig
-------------+------------------------------------------
 report_ro   | {effective_cache_size=32GB}
(1 row)

不要让角色级设置与全局设置矛盾

若全局是 48GB、报表角色是 32GB，会出现「同一个查询在不同账号下计划不同」。这在排查问题时极易混淆。

推荐：全局统一设置，只在有明确、可解释的理由时才做角色级覆盖。

八、与其他优化器参数的联合调优 ​

effective_cache_size 必须与 random_page_cost 一起看。下面给出两种典型服务器配置：

HDD（机械盘）：

ini
effective_cache_size = '32GB'       # 内存的 50%
random_page_cost = 4.0              # 保持默认
seq_page_cost = 1.0
effective_io_concurrency = 2        # 机械盘并发低

SSD/NVMe：

ini
effective_cache_size = '48GB'       # 内存的 75%
random_page_cost = 1.1              # 随机读几乎等于顺序读
seq_page_cost = 1.0
effective_io_concurrency = 200      # 高并发

验证方式：

sql
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
text
                                     QUERY PLAN
----------------------------------------------------------------------------------------
 Index Scan using idx_orders_customer on orders  (cost=0.43..204.11 rows=412 width=120)
   (actual time=0.031..2.114 rows=408 loops=1)
   Index Cond: (customer_id = 12345)
   Buffers: shared hit=214 read=21
 Planning Time: 0.412 ms
 Execution Time: 2.221 ms
(5 rows)

shared hit=214 read=21 说明 214 页命中缓存、21 页真实读盘——这个比例才是判断 effective_cache_size 是否接近真实的实证依据。

九、pg_statio 与 BUFFERS 的配合使用 ​

sql
-- 单条查询的实际缓存命中情况
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders WHERE created_at >= '2026-01-01';
text
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate  (cost=192113.44..192113.45 rows=1 width=8) (actual time=1842.113..1842.114 rows=1 loops=1)
   Buffers: shared hit=48211 read=56211
   ->  Index Only Scan using idx_orders_created_at on orders  (cost=0.43..184201.20 rows=1048512 width=0)
         (actual time=0.042..1620.441 rows=1048442 loops=1)
         Index Cond: (created_at >= '2026-01-01 00:00:00'::timestamp without time zone)
         Heap Fetches: 12441
         Buffers: shared hit=48211 read=56211
 Planning Time: 1.221 ms
 Execution Time: 1845.882 ms
(7 rows)
text
shared hit=48211, read=56211
命中率 = 48211 / (48211 + 56211) = 46.2%

46.2% 的命中率说明这个查询读取的数据有超过一半需要从 OS/磁盘取——如果这是一个频繁执行的查询，说明 effective_cache_size 设得可能过于乐观（优化器以为大部分在缓存里，所以选了索引扫描；实际一半要读盘）。

反过来，如果常见查询的命中率都在 95%+，说明当前的 effective_cache_size 是合理的。

十、pg_stat_statements 层面的观察 ​

把 effective_cache_size 与查询级别的 I/O 统计结合起来：

sql
SELECT calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       shared_blks_hit, shared_blks_read,
       round(shared_blks_hit * 100.0 /
             nullif(shared_blks_hit + shared_blks_read, 0), 2) AS hit_pct,
       left(query, 60) AS query
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 10;
text
 calls | total_ms  | mean_ms | shared_blks_hit | shared_blks_read | hit_pct |                        query
-------+-----------+---------+-----------------+------------------+---------+------------------------------------------------------
  1821 | 1284219.33|  705.12 |       821432112 |          4821112 |   99.42 | SELECT ... FROM orders WHERE created_at >= $1
   921 |  421882.11|  457.96 |        82143211 |           881211 |   98.94 | SELECT ... FROM order_items WHERE ...
   441 |   82112.44|  186.20 |        42188211 |           121882 |   99.71 | SELECT ... FROM customers WHERE ...
(3 rows)

用 pg_stat_statements 找「命中率低的热查询」

命中率低 + 调用次数多的查询，就是缓存估算不准的受害者或贡献者。把这些查询的 shared_blks_read 加起来与总读量对比，能判断 effective_cache_size 是否被误设。

sql
-- 总读量的构成
SELECT sum(shared_blks_read) AS total_disk_reads,
       sum(shared_blks_hit)  AS total_cache_hits
FROM pg_stat_statements;

十一、参数作用范围的完整链路 ​

sql
-- 查看 effective_cache_size 的所有来源层级
SELECT name,
       setting,
       reset_val,
       source,
       sourcefile,
       sourceline
FROM pg_settings
WHERE name = 'effective_cache_size';
text
         name          | setting | reset_val |       source       |               sourcefile                | sourceline
-----------------------+---------+-----------+--------------------+-----------------------------------------+------------
 effective_cache_size  | 48GB    | 48GB      | configuration file | /var/lib/postgresql/18/main/postgresql.conf |         88
(1 row)

若是会话内改过：

sql
SET effective_cache_size = '96GB';
SELECT setting, reset_val, source FROM pg_settings WHERE name = 'effective_cache_size';
text
 setting | reset_val | source
---------+-----------+---------
 96GB    | 48GB      | session
(1 row)

source = session 说明被会话覆盖。排查「同一个查询在不同会话计划不同」时，这是关键线索：

sql
-- 找出手动改过这个参数的会话
SELECT pid, usename, application_name, query
FROM pg_stat_activity
WHERE backend_type = 'client backend';

实战示例 ​

场景：修复「有索引却走全表扫描」的性能问题 ​

某查询 SELECT * FROM orders WHERE customer_id = 12345 在 customer_id 上有 B-tree 索引，但 EXPLAIN 显示走 Seq Scan，单次耗时 800ms。

第 1 步：确认计划与代价。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 12345;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..482193.11 rows=412 width=120) (actual time=182.114..812.442 rows=408 loops=1)
   Filter: (customer_id = 12345)
   Rows Removed by Filter: 9999592
   Buffers: shared hit=4211 read=118221
 Planning Time: 0.912 ms
 Execution Time: 815.221 ms
(6 rows)

问题明确：

  • 走 Seq Scan，扫描了 1000 万行（Rows Removed by Filter: 9999592）。
  • read=118221 页真实读盘，命中率 = 4211 / 122432 = 3.4%——存储层几乎全是冷读。

第 2 步：检查索引是否可用。

sql
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY indexrelname;
text
         indexrelname         | idx_scan | idx_tup_read | idx_tup_fetch | idx_size
------------------------------+----------+--------------+---------------+----------
 idx_orders_created_at        | 18221331 |    821432112 |     121882113 | 4096 MB
 idx_orders_customer          |        0 |            0 |             0 | 1024 MB
 orders_pkey                  |  8812132 |    821432112 |     121882113 | 2048 MB
(3 rows)

idx_orders_customer 的 idx_scan = 0——从未被使用过。索引存在但优化器不用。

第 3 步：检查优化器参数。

sql
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('effective_cache_size', 'random_page_cost', 'seq_page_cost', 'cpu_tuple_cost', 'cpu_index_tuple_cost');
text
         name          | setting | unit |       source
-----------------------+---------+------+--------------------
 cpu_index_tuple_cost  | 0.005   |      | default
 cpu_tuple_cost        | 0.01    |      | default
 effective_cache_size  | 524288  | 8kB  | default
 random_page_cost      | 4       |      | default
 seq_page_cost         | 1       |      | default
(3 rows)

全是默认值。effective_cache_size = 4GB 是默认值。

第 4 步：检查机器实际内存与表大小，判断合理值。

bash
free -h
text
               total        used        free      shared  buff/cache   available
Mem:            62Gi        21Gi       1.8Gi       8.1Gi        39Gi        37Gi
Swap:          2.0Gi          0B       2.0Gi
sql
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS orders_total,
       pg_size_pretty(pg_relation_size('orders'))       AS orders_table,
       pg_size_pretty(pg_indexes_size('orders'))        AS orders_indexes,
       pg_size_pretty((SELECT setting::bigint * 8192 FROM pg_settings WHERE name='shared_buffers')::bigint) AS shared_buffers;
text
 orders_total | orders_table | orders_indexes | shared_buffers
--------------+--------------+----------------+----------------
 24 GB        | 16 GB        | 8192 MB        | 8192 MB

机器 62GB 内存，orders 表 16GB。表大小 16GB 远大于默认的 effective_cache_size = 4GB，所以优化器认为索引扫描时几乎每次都要读盘，随机读代价 × 4.0 让 Seq Scan 更便宜。

第 5 步：验证假设——临时调大 effective_cache_size 看计划是否改变。

sql
BEGIN;
SET LOCAL effective_cache_size = '46GB';
EXPLAIN (COSTS) SELECT * FROM orders WHERE customer_id = 12345;
COMMIT;
text
                                     QUERY PLAN
---------------------------------------------------------------------------------------
 Index Scan using idx_orders_customer on orders  (cost=0.43..1842.11 rows=412 width=120)
(1 row)

计划已变成 Index Scan！假设成立。

第 6 步：进一步验证实际性能提升。

sql
BEGIN;
SET LOCAL effective_cache_size = '46GB';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 12345;
COMMIT;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Index Scan using idx_orders_customer on orders  (cost=0.43..1842.11 rows=412 width=120)
   (actual time=0.058..1.842 rows=408 loops=1)
   Index Cond: (customer_id = 12345)
   Buffers: shared hit=214 read=24
 Planning Time: 0.412 ms
 Execution Time: 2.021 ms
(5 rows)

耗时从 815ms 降到 2ms，提升 400 倍；真实读盘从 118221 页降到 24 页。

第 7 步：正式配置（含 SSD 参数配套）。

sql
-- 机器确认是 SSD，一并调整 random_page_cost
ALTER SYSTEM SET effective_cache_size = '46GB';
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
text
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
sql
-- 核验（这些都是 user 上下文，reload 即生效，不需要重启）
SELECT name, setting, unit, source, sourcefile, pending_restart
FROM pg_settings
WHERE name IN ('effective_cache_size', 'random_page_cost', 'effective_io_concurrency')
ORDER BY name;
text
         name            | setting | unit |       source       |                  sourcefile                   | pending_restart
-------------------------+---------+------+--------------------+-----------------------------------------------+-----------------
 effective_cache_size    | 5898240 | 8kB  | configuration file | /var/lib/postgresql/18/main/postgresql.auto.conf | f
 effective_io_concurrency| 200     |      | configuration file | /var/lib/postgresql/18/main/postgresql.auto.conf | f
 random_page_cost        | 1.1     |      | configuration file | /var/lib/postgresql/18/main/postgresql.auto.conf | f
(3 rows)

pending_restart 全为 f，无需重启。

第 8 步：验证线上查询。

sql
-- 新建连接以拿到新配置快照
bash
psql -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 12345;"
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Index Scan using idx_orders_customer on orders  (cost=0.43..204.11 rows=412 width=120)
   (actual time=0.052..1.914 rows=408 loops=1)
   Index Cond: (customer_id = 12345)
   Buffers: shared hit=214 read=24
 Planning Time: 0.398 ms
 Execution Time= 1.988 ms
(5 rows)

第 9 步：观察整体影响（24 小时后）。

sql
SELECT indexrelname, idx_scan, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;
text
         indexrelname         | idx_scan | idx_tup_fetch
------------------------------+----------+---------------
 idx_orders_customer          |  1822113 |        9211442   ◀── 从 0 变为 182 万
 idx_orders_created_at        | 18421331 |      821432112
 orders_pkey                  |  8842132 |      121882113
(3 rows)

idx_orders_customer 的 idx_scan 从 0 涨到 182 万，索引终于被用起来了。

sql
-- 对比整体 I/O
SELECT datname, blks_read, blks_hit,
       round(blks_hit * 100.0 / nullif(blks_hit + blks_read, 0), 3) AS hit_pct
FROM pg_stat_database WHERE datname = 'myapp';
text
 datname | blks_read |  blks_hit   | hit_pct
---------+-----------+-------------+---------
 myapp   |  8821132  | 42188211332 |  99.979
(1 row)

第 10 步：固化经验并建立监控。

sql
-- 监控：定期检查优化器参数是否被误改
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('effective_cache_size', 'random_page_cost', 'seq_page_cost', 'effective_io_concurrency')
ORDER BY name;
sql
-- 监控：找出「有索引但从未被扫描」的索引
SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;
text
 schemaname | relname |   indexrelname    | idx_scan | idx_size
------------+---------+-------------------+----------+----------
 public     | orders  | idx_orders_legacy |        0 | 512 MB
 public     | sessions| idx_sessions_ua   |        0 | 128 MB
(2 rows)

用「从未使用的索引」反查参数问题

大量索引 idx_scan = 0 且这些索引「理论上该被用」，往往意味着 effective_cache_size 偏低或 random_page_cost 偏高。这是一个非常好用的反查信号。

注意事项 ​

不要以为 effective_cache_size 会分配内存。 它只是估算值，改它不会有任何内存占用变化。

sql
-- 错误理解：认为设成 1TB 会占 1TB 内存
SET effective_cache_size = '1TB';
-- 实际内存占用不变

不要把它设成「shared_buffers 的值」。 它应该包含 shared_buffers 加上 OS 缓存，通常远大于 shared_buffers。

ini
# 不推荐（漏掉了 OS 缓存部分）
shared_buffers = '16GB'
effective_cache_size = '16GB'
# 推荐
effective_cache_size = '48GB'

不要在容器里按宿主机内存算。 会严重高估，导致优化器过度偏好索引。

ini
# 错误！容器限额 8Gi，却按 62GB 宿主机算
effective_cache_size = '46GB'

不要忘记在内存扩容后同步调整它。 内存从 32GB 升到 128GB，effective_cache_size 还停在旧值，会让优化器过度保守。

sql
-- 内存变更后审计
SELECT name, setting, unit FROM pg_settings WHERE name = 'effective_cache_size';
-- 对比实际内存

不要只改 effective_cache_size 而不看 random_page_cost。 两者共同决定索引扫描代价，单独改一个可能造成新的误判。

sql
-- 不推荐：只调一个
ALTER SYSTEM SET effective_cache_size = '48GB';
-- 推荐：配套调整
ALTER SYSTEM SET effective_cache_size = '48GB';
ALTER SYSTEM SET random_page_cost = 1.1;      -- SSD 场景

不要把 effective_cache_size 设得远超物理内存。 会让优化器以为索引扫描几乎零 I/O 成本，产生大量实际随机读。

ini
# 危险！62GB 内存却设 4TB
effective_cache_size = '4TB'

不要在未验证的情况下就全局修改。 先用 SET LOCAL 在事务里验证计划是否真的变好。

sql
-- 安全验证方式
BEGIN;
SET LOCAL effective_cache_size = '48GB';
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
ROLLBACK;

不要忽略 effective_cache_size 是「每个实例」而非「每个数据库」。 多库实例中它只设一个值，无法为单个库单独调优（除非用角色/会话级覆盖）。

sql
-- 只能通过角色/库级覆盖做差异化
ALTER DATABASE analytics SET effective_cache_size = '96GB';

不要指望改完立刻在所有会话生效。 它是 user 上下文，已有会话的配置快照可能还是旧值。

sql
-- 已有会话需要 RESET 才会拿到新值
RESET effective_cache_size;
SHOW effective_cache_size;

不要把 blks_hit 命中率高当作「参数设对了」的证据。 它只说明缓存有效，不说明数值精确。

sql
-- 更可靠的验证：看 BUFFERS 输出与索引使用率
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
SELECT count(*) FROM pg_stat_user_indexes WHERE idx_scan = 0;

不要在机械盘上照抄 SSD 的参数。 random_page_cost = 1.1 在机械盘上会导致优化器误选索引扫描，产生海量随机 I/O。

ini
# HDD 场景：保持 random_page_cost = 4.0
random_page_cost = 4.0
effective_io_concurrency = 2

不要忽略云环境（EBS/云盘）的特殊性。 云盘的随机读性能介于 HDD 与本地 SSD 之间，random_page_cost 常取 2.0~3.0，需要实测。

sql
-- 通过实际执行的 BUFFERS 与耗时反推合适值
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

不要在事务里用 SET（非 LOCAL）后忘记它会影响整个会话。 后续所有查询都用被改的值。

sql
-- 危险：污染整个会话
SET effective_cache_size = '1TB';
-- 后续所有查询都基于错误假设
SET effective_cache_size = DEFAULT;   -- 记得还原

最佳实践 ​

设为系统内存的 50%~75%（专用数据库服务器取 75%）。 这是最实用的起点。

ini
# 64GB 内存
effective_cache_size = '48GB'

容器环境按 cgroup 限额算，并核对容器内看到的真实限额。

bash
cat /sys/fs/cgroup/memory.max
ini
# 8GB 限额
effective_cache_size = '6GB'

与 random_page_cost 配套调整，按存储介质选值。

ini
# SSD / NVMe
effective_cache_size = '48GB'
random_page_cost = 1.1
effective_io_concurrency = 200

改之前先用 SET LOCAL 在事务里做 A/B 对比。 安全、可回滚、有实证。

sql
BEGIN;
SET LOCAL effective_cache_size = '48GB';
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
ROLLBACK;

用 EXPLAIN (ANALYZE, BUFFERS) 的 hit / read 比验证估算准确性。 若常见查询的 read 占比很高，说明参数过于乐观。

sql
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- 看 Buffers: shared hit=X read=Y

用「从未使用的索引」清单反查参数是否偏保守。

sql
SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

内存/存储变更后，把 effective_cache_size 与 random_page_cost 一起重新评估。 硬件变了，参数必须跟着变。

sql
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('effective_cache_size', 'random_page_cost', 'effective_io_concurrency');

全局统一设置，避免角色级覆盖造成「同查询不同计划」。 只在有明确理由时做角色级覆盖，并在注释中写明。

ini
# postgresql.conf
effective_cache_size = '48GB'

用 pg_stat_statements 找「命中率低但调用频繁」的查询，作为参数校准的输入。

sql
SELECT queryid, calls,
       round(shared_blks_hit * 100.0 / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS hit_pct,
       left(query, 80)
FROM pg_stat_statements
WHERE calls > 100 AND shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 20;

把优化器参数纳入监控与告警。 参数被误改是「计划突然变差」的高频原因，值得监控。

sql
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('effective_cache_size', 'random_page_cost', 'seq_page_cost',
               'cpu_tuple_cost', 'cpu_index_tuple_cost', 'cpu_operator_cost')
ORDER BY name;

把参数值写进 postgresql.conf 并纳入版本库。 便于 code review 与历史追溯。

ini
# postgresql.conf（纳入 git）
# ---- 优化器内存估算 ----
effective_cache_size = '48GB'
# ---- 存储介质相关（SSD） ----
random_page_cost = 1.1
effective_io_concurrency = 200

改动后用 pending_restart 确认无需重启。

sql
SELECT name, pending_restart FROM pg_settings WHERE pending_restart;
-- 应为 0 行

定期（如每季度）复核一次参数是否仍匹配硬件。 硬件升级、数据量增长都会让旧参数失准。

sql
-- 复核清单
SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;
SELECT name, setting, unit FROM pg_settings WHERE name = 'effective_cache_size';

下一节

继续学习：work_mem 工作内存

参考链接 ​