调整查询规划器成本常量
学习 random_page_cost 和 effective_cache_size 等规划器成本常量如何影响计划选择，以及如何根据硬件和数据进行调整
调整查询规划器成本常量 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。
本课时的部分内容尚未翻译，以英文显示。
The Planner Needs Hints About Hardware
The planner estimates the cost of each plan using constants that model your hardware. If those constants do not match reality, it may pick a sequential scan when an index scan would be far faster, or vice versa.
seq_page_cost vs random_page_cost
Two key constants:
seq_page_cost(default 1.0): cost to read a page sequentiallyrandom_page_cost(default 4.0): cost to read a page at random
The 4:1 ratio assumes spinning disks where random reads are slow.
Tuning for SSDs
On SSDs random reads are nearly as cheap as sequential ones. Lowering random_page_cost makes the planner favor index scans appropriately.
SET random_page_cost = 1.1;effective_cache_size
This tells the planner how much memory the OS and PostgreSQL together are likely to use for caching data. It does not allocate memory — it only influences cost estimates for index scans.
SET effective_cache_size = '12GB';Why effective_cache_size Matters
A large value tells the planner that repeated index lookups will likely hit cache, making index scans look cheaper and more attractive for large tables. Set it to roughly 50-75% of total RAM.
CPU Cost Constants
Finer constants weigh CPU work:
cpu_tuple_cost: per-row processingcpu_index_tuple_cost: per index entrycpu_operator_cost: per operator/function call
These are rarely changed but matter for CPU-heavy expressions.
Testing a Change at Session Level
Test constants with SET in your session before making them permanent. Compare plans with EXPLAIN.
SET random_page_cost = 1.1;
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;Making It Permanent
Once satisfied, set the value cluster-wide and reload, or edit postgresql.conf.
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();Beware of Over-Tuning
Do not chase a single slow query by skewing constants globally — you may break thousands of other plans. Fix the root cause (missing index, stale stats) first; adjust constants only for genuine hardware mismatches.
Per-Tablespace Tuning
If some data sits on SSD and some on HDD, you can set random_page_cost per tablespace so the planner reasons about each storage medium correctly.
ALTER TABLESPACE fast_ssd
SET (random_page_cost = 1.1);Verifying the Effect
After changing a constant, confirm the planner actually switched plans. Run EXPLAIN before and after and look for a Seq Scan turning into an Index Scan (or vice versa).
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
SET random_page_cost = 1.1;
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;Quick Check
Test your planner-tuning knowledge.
Recap
You learned planner cost tuning:
- Cost constants model your hardware for the planner
- Lower
random_page_coston SSDs to favor index scans - Set
effective_cache_sizeto ~50-75% of RAM - Test with
SETand EXPLAIN beforeALTER SYSTEM - Fix indexes/stats before skewing constants globally
常见问题解答
「调整查询规划器成本常量」课时是免费的吗？
是的 — 「调整查询规划器成本常量」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。
「调整查询规划器成本常量」这节课中我会学到什么？
学习 random_page_cost 和 effective_cache_size 等规划器成本常量如何影响计划选择，以及如何根据硬件和数据进行调整 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 PostgreSQL Performance & Query Optimization 需要有经验吗？
无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。
「调整查询规划器成本常量」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？
能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。