PostgreSQL Supports Specifying Optimizer Cost Parameters When Creating Tablespaces

PostgreSQL Supports Specifying Optimizer Cost Parameters When Creating Tablespaces

PostgreSQL supports specifying optimizer cost parameters when creating tablespaces, directly influencing query cost estimation. This targets the optimizer accuracy problem in “hybrid storage” environments (e.g., hot data on NVMe SSDs, cold data on HDDs). In the WITH clause of CREATE TABLESPACE, you can set the following I/O cost-related parameters:

• seq_page_cost: the cost of sequentially reading one disk page.
• random_page_cost: the cost of randomly reading one disk page (the most critical one).
• effective_io_concurrency: the effective number of concurrent I/O operations (very important for SSDs).
• maintenance_io_concurrency: the effective number of concurrent I/O operations during maintenance operations (such as VACUUM).

In PostgreSQL, a tablespace is used to let the user decide where the data files for a given database object should reside on the filesystem. A tablespace corresponds to a directory on the filesystem. When creating a tablespace, the directory must be empty. Optimizer-related cost parameter options can be specified when creating the tablespace:

postgres=# create tablespace myspc location '/home/postgres/myspc' with (seq_page_cost = 2.0,random_page_cost = 8.0);
CREATE TABLESPACE
postgres=# select * from pg_tablespace ;
  oid  |  spcname   | spcowner | spcacl |                spcoptions                
-------+------------+----------+--------+------------------------------------------
  1663 | pg_default |       10 |        | 
  1664 | pg_global  |       10 |        | 
 16477 | myspc      |       10 |        | {seq_page_cost=2.0,random_page_cost=8.0}
(3 rows)

The specific options are: random_page_cost, seq_page_cost, effective_io_concurrency, and maintenance_io_concurrency.

typedef struct TableSpaceOpts
{
	int32		vl_len_;		/* varlena header (do not touch directly!) */
	float8		random_page_cost;
	float8		seq_page_cost;
	int			effective_io_concurrency;
	int			maintenance_io_concurrency;
} TableSpaceOpts;

When estimating costs, the cost is calculated based on the tablespace options. The function get_tablespace_page_costs is called to obtain spc_seq_page_cost — the sequential-scan page cost for that tablespace.

/*
 * get_tablespace_page_costs
 *		Returns the random and sequential page access costs
 *		for a specified tablespace.
 *
 *		This value is not protected by transactional locking, so it
 *		may still change after a running SELECT has finished planning
 *		using these values.
 */
void get_tablespace_page_costs(Oid spcid,
						  double *spc_random_page_cost,
						  double *spc_seq_page_cost)
{
	TableSpaceCacheEntry *spc = get_tablespace(spcid);  // get cached tablespace info

	if (spc_random_page_cost)    // random-scan page cost
	{
		if (!spc->opts || spc->opts->random_page_cost < 0)
			*spc_random_page_cost = random_page_cost;
		else
			*spc_random_page_cost = spc->opts->random_page_cost;
	}

	if (spc_seq_page_cost)   // sequential-scan page cost
	{
		if (!spc->opts || spc->opts->seq_page_cost < 0)
			*spc_seq_page_cost = seq_page_cost;
		else
			*spc_seq_page_cost = spc->opts->seq_page_cost;
	}
}

For example, the cost calculation for a sequential scan:

/*
 * cost_seqscan
 *		Computes and returns the cost of a sequential scan of a relation.
 *
 * 'baserel' is the relation being scanned
 * 'param_info' is the corresponding ParamPathInfo if this is a
 *		parameterized path, otherwise NULL
 */
void
cost_seqscan(Path *path, PlannerInfo *root,
			 RelOptInfo *baserel, ParamPathInfo *param_info)
{
	Cost		startup_cost = 0;
	Cost		cpu_run_cost;
	Cost		disk_run_cost;
	double		spc_seq_page_cost;
	QualCost	qpqual_cost;
	Cost		cpu_per_tuple;

	/* Should only be applied to base relations */
	Assert(baserel->relid > 0);
	Assert(baserel->rtekind == RTE_RELATION);

	/* Mark the path with the correct row estimate */
	if (param_info)
		path->rows = param_info->ppi_rows;
	else
		path->rows = baserel->rows;

	/* Get the estimated page costs for the tablespace containing this table */
	get_tablespace_page_costs(baserel->reltablespace,
							  NULL,
							  &spc_seq_page_cost);

	/*
	 * Disk costs
	 */
	disk_run_cost = spc_seq_page_cost * baserel->pages;

	/* CPU costs */
	get_restriction_qual_cost(root, baserel, param_info, &qpqual_cost);

	startup_cost += qpqual_cost.startup;
	cpu_per_tuple = cpu_tuple_cost + qpqual_cost.per_tuple;
	cpu_run_cost = cpu_per_tuple * baserel->tuples;
	/* tlist evaluation cost is paid per output row, not per scanned tuple */
	startup_cost += path->pathtarget->cost.startup;
	cpu_run_cost += path->pathtarget->cost.per_tuple * path->rows;

	/* Adjust the cost accordingly if parallelism is used. */
	if (path->parallel_workers > 0)
	{
		double		parallel_divisor = get_parallel_divisor(path);

		/* CPU cost is amortized across all worker processes. */
		cpu_run_cost /= parallel_divisor;

		/*
		 * Perhaps a portion of the I/O cost could be amortized, but
		 * the amortized amount would likely be small because most
		 * operating systems already perform aggressive read-ahead.
		 * For now we assume that the disk run cost cannot be
		 * amortized at all.
		 */

		/*
		 * For parallel plans, the row count needs to represent the
		 * number of tuples processed by each worker process.
		 */
		path->rows = clamp_row_est(path->rows / parallel_divisor);
	}

	path->disabled_nodes = enable_seqscan ? 0 : 1;
	path->startup_cost = startup_cost;
	path->total_cost = startup_cost + cpu_run_cost + disk_run_cost;
}

Usage Example

Suppose you have two types of storage:

• HDD (mechanical hard drive): slow random reads, high cost.
• NVMe SSD: ultra-fast random reads, approaching sequential reads.

You can create two tablespaces like this:

-- 1. Create a tablespace on the high-speed SSD
-- Set random I/O cost to 1.0 (same as sequential I/O), and set high concurrency
CREATE TABLESPACE ssd_fast
LOCATION '/mnt/nvme/pgdata/ssd'
WITH (
    seq_page_cost = 1.0,
    random_page_cost = 1.0,      -- SSDs have fast random I/O; the default of 4.0 is unnecessary
    effective_io_concurrency = 200 -- supports high-concurrency I/O
);

-- 2. Create a tablespace on the slow HDD
-- Use the default high random I/O cost
CREATE TABLESPACE hdd_cold
LOCATION '/mnt/hdd/pgdata/cold'
WITH (
    seq_page_cost = 1.0,
    random_page_cost = 4.0,      -- mechanical drives have slow random reads; keep the cost high
    effective_io_concurrency = 2 -- mechanical drives have weak concurrency
);

Without this feature, random_page_cost was a global parameter (defaulting to 4.0). This created a dilemma:

• If your database was mainly on SSDs, you would want it to be 1.0 so that the optimizer favors index scans.
• But if some tables were on HDDs, a cost of 1.0 would mislead the optimizer into thinking HDD random reads were also fast, potentially causing it to choose many index scans that are terribly unfriendly to HDDs — leading to a performance disaster.

The solution: when you create a table or index and specify TABLESPACE ssd_fast, the optimizer automatically reads the random_page_cost = 1.0 defined for that tablespace. Conversely, for a table specifying TABLESPACE hdd_cold, the optimizer uses 4.0.

The result:

• For tables on SSDs, the optimizer more aggressively chooses Index Scans.
• For tables on HDDs, the optimizer prefers Sequential Scans, avoiding expensive random I/O.