PostgreSQL支持在创建表空间时指定优化器代价参数

PostgreSQL支持在创建表空间时指定优化器代价参数，用于影响查询代价估算。旨在解决“混合存储”环境（例如：热数据在 NVMe SSD，冷数据在 HDD）下的优化器精准度问题。在CREATE TABLESPACE的 WITH子句中，你可以设置以下与 I/O 成本相关的参数：

seq_page_cost：顺序读取一个磁盘页的代价。
random_page_cost：随机读取一个磁盘页的代价（最关键）。
effective_io_concurrency：有效的并发 I/O 数量（对 SSD 非常重要）。
maintenance_io_concurrency：维护操作（如 VACUUM）时的有效并发 I/O 数量。

PostgreSQL中的表空间用于让用户决定给定数据库对象的数据文件在文件系统中的存放位置。一个表空间对应文件系统上的一个目录。在创建表空间时，该目录必须为空。创建表空间时可指定该表空间的优化器相关代价参数选项：

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)

具体选项有：random_page_cost，seq_page_cost，effective_io_concurrency，maintenance_io_concurrency。

typedef struct TableSpaceOpts
{
	int32		vl_len_;		/* varlena 头部（请勿直接操作！） */
	float8		random_page_cost;
	float8		seq_page_cost;
	int			effective_io_concurrency;
	int			maintenance_io_concurrency;
} TableSpaceOpts;

在估计代价时，会根据表空间的选项来计算代价。会通过调用get_tablespace_page_costs函数获取spc_seq_page_cost该表空间的顺序扫描页面代价。

/*
 * get_tablespace_page_costs
 *		返回指定表空间的随机/顺序页面访问代价。
 *
 *		此值不受事务锁定保护，因此在正在执行的一条 SELECT 使用这些值
 *		完成计划制定之后，此值仍有可能被修改。
 */
void get_tablespace_page_costs(Oid spcid,
						  double *spc_random_page_cost,
						  double *spc_seq_page_cost)
{
	TableSpaceCacheEntry *spc = get_tablespace(spcid);  // 获取表空间的缓存信息

	if (spc_random_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)   // 顺序扫描页面代价
	{
		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;
	}
}

例如顺序扫描的代价计算：

/*
 * cost_seqscan
 *		计算并返回顺序扫描一个关系的代价。
 *
 * 'baserel' 是被扫描的关系
 * 'param_info' 如果是参数化路径则为对应的 ParamPathInfo，否则为 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;

	/* 只应应用于基表关系 */
	Assert(baserel->relid > 0);
	Assert(baserel->rtekind == RTE_RELATION);

	/* 用正确的行估计值标记该路径 */
	if (param_info)
		path->rows = param_info->ppi_rows;
	else
		path->rows = baserel->rows;

	/* 获取包含该表的表空间所估计的页面代价 */
	get_tablespace_page_costs(baserel->reltablespace,
							  NULL,
							  &spc_seq_page_cost);

	/*
	 * 磁盘代价
	 */
	disk_run_cost = spc_seq_page_cost * baserel->pages;

	/* CPU 代价 */
	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 求值代价按每个输出行支付，而不是按每个被扫描的元组支付 */
	startup_cost += path->pathtarget->cost.startup;
	cpu_run_cost += path->pathtarget->cost.per_tuple * path->rows;

	/* 如果使用了并行，则相应调整代价。 */
	if (path->parallel_workers > 0)
	{
		double		parallel_divisor = get_parallel_divisor(path);

		/* CPU 代价会被分摊到所有工作进程上。 */
		cpu_run_cost /= parallel_divisor;

		/*
		 * 或许可以对一部分 I/O 代价进行分摊，但分摊量可能很小，
		 * 因为大多数操作系统已经做了积极的预读。目前我们假设
		 * 磁盘运行代价完全无法被分摊。
		 */

		/*
		 * 对于并行计划，行数需要表示每个工作进程处理的元组数量。
		 */
		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;
}

使用示例

假设你有两种存储：

HDD（机械硬盘）：随机读取慢，代价高。
NVMe SSD：随机读取极快，接近顺序读取。你可以这样创建两个表空间：

-- 1. 创建位于高速 SSD 上的表空间
-- 随机 I/O 代价设为 1.0（与顺序 I/O 相同），并设置高并发
CREATE TABLESPACE ssd_fast
LOCATION '/mnt/nvme/pgdata/ssd'
WITH (
    seq_page_cost = 1.0,
    random_page_cost = 1.0,      -- SSD 随机读写很快，不需要 4.0 的默认值
    effective_io_concurrency = 200 -- 支持高并发 I/O
);

-- 2. 创建位于慢速 HDD 上的表空间
-- 使用默认的高昂随机 I/O 代价
CREATE TABLESPACE hdd_cold
LOCATION '/mnt/hdd/pgdata/cold'
WITH (
    seq_page_cost = 1.0,
    random_page_cost = 4.0,      -- 机械硬盘随机读取慢，保持高代价
    effective_io_concurrency = 2 -- 机械硬盘并发能力弱
);

在没有该功能之前，random_page_cost是一个全局参数（默认为 4.0）。这导致了一个两难的局面：

如果你的数据库主要在 SSD 上，你希望它是 1.0，以便优化器更倾向于使用索引扫描。
但如果你有些表在 HDD 上，1.0 的代价会让优化器误以为 HDD 的随机读取也很快，从而可能错误地选择了对 HDD 极不友好的大量索引扫描，导致性能灾难。

解决方案：当你创建表或索引并指定 TABLESPACE ssd_fast 时，优化器会自动读取该表空间定义的 random_page_cost = 1.0。相反，指定 TABLESPACE hdd_cold 的表，优化器会使用 4.0。

结果：

对于 SSD 上的表，优化器会更积极地选择索引扫描（Index Scan）。
对于 HDD 上的表，优化器会更倾向于顺序扫描（Seq Scan），避免昂贵的随机 I/O。