การปรับค่าคงที่ต้นทุนของตัววางแผนการสืบค้น

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.

Two key constants:

• seq_page_cost (default 1.0): cost to read a page sequentially
• random_page_cost (default 4.0): cost to read a page at random

The 4:1 ratio assumes spinning disks where random reads are slow.

On SSDs random reads are nearly as cheap as sequential ones. Lowering random_page_cost makes the planner favor index scans appropriately.

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.

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.

Finer constants weigh CPU work:

• cpu_tuple_cost: per-row processing
• cpu_index_tuple_cost: per index entry
• cpu_operator_cost: per operator/function call

These are rarely changed but matter for CPU-heavy expressions.

Test constants with SET in your session before making them permanent. Compare plans with EXPLAIN.

Once satisfied, set the value cluster-wide and reload, or edit postgresql.conf.

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.

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.

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).

You learned planner cost tuning:

• Cost constants model your hardware for the planner
• Lower random_page_cost on SSDs to favor index scans
• Set effective_cache_size to ~50-75% of RAM
• Test with SET and EXPLAIN before ALTER SYSTEM
• Fix indexes/stats before skewing constants globally