假设你正坐在数据高速公路的收费站里，手里攥着两种通行卡：一种是"本地户籍卡"（PL/pgSQL变量），另一种是"临时通行卡"（SQL参数）。同样都是通行凭证，但当车辆（查询请求）通过时，收费员（查询优化器）的检查方式却大不相同。今天我们就用具体示例打开PostgreSQL执行计划的魔法黑箱，看看这两类变量是如何影响查询性能的。
1. 知识小课堂：基础认知准备
1.1 PL/pgSQL变量的特性
-- 典型的存储过程变量声明
CREATE OR REPLACE FUNCTION calculate_order_stats(order_year INT)
RETURNS TABLE (total_amount NUMERIC, avg_price NUMERIC) AS $$
DECLARE
base_rate CONSTANT NUMERIC := 0.95; -- 声明为常量
dynamic_rate NUMERIC; -- 普通变量
BEGIN
SELECT discount_rate
INTO dynamic_rate -- 从表中获取值
FROM rate_settings
WHERE year = order_year;
RETURN QUERY
SELECT SUM(amount * base_rate),
AVG(unit_price * dynamic_rate)
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = order_year;
END;
$$ LANGUAGE plpgsql;
这些变量在编译阶段就会被确定数据类型，且在函数内部始终保持可见性。就像给物品贴上的标签，在进入仓库（函数执行）时就确定了存放的位置（内存地址）。
1.2 SQL参数的本质
-- 参数化查询示例（使用JDBC风格的占位符）
PREPARE order_query (INT) AS
SELECT product_id, COUNT(*)
FROM order_details
WHERE order_id IN (
SELECT order_id
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = $1
)
GROUP BY product_id;
EXECUTE order_query(2023);
这种参数在传输时就像通过快递包裹传输物品，每次执行都会带着新的包装箱（参数值）到仓库，但包裹的形状（参数类型）在打包时（准备语句时）就已经确定。
2. 真实案例大揭秘：执行计划差异全景观察
2.1 值传递引发的规划器困惑
-- 案例1：PL/pgSQL中的变量导致执行计划固化
CREATE OR REPLACE FUNCTION get_user_orders(user_id INT)
RETURNS SETOF orders AS $$
DECLARE
target_id ALIAS FOR $1; -- 别名变量
BEGIN
RETURN QUERY
SELECT *
FROM orders
WHERE user_id = target_id -- 使用PL/pgSQL变量
ORDER BY order_date DESC;
END;
$$ LANGUAGE plpgsql;
-- 使用不同参数值调用时：
EXPLAIN ANALYZE SELECT * FROM get_user_orders(123);
EXPLAIN ANALYZE SELECT * FROM get_user_orders(456);
-- 案例2：直接使用参数化查询
PREPARE dynamic_user_orders (INT) AS
SELECT *
FROM orders
WHERE user_id = $1
ORDER BY order_date DESC;
-- 对比执行计划
EXECUTE dynamic_user_orders(123);
EXECUTE dynamic_user_orders(456);
PL/pgSQL版本的两个调用会产生完全相同的执行计划，即使实际数据分布差异很大（比如用户123有3条订单，用户456有300万条订单）。而参数化查询的执行计划会根据首次执行时的参数生成，但当参数值变化较大时，后续可能重新生成计划。
2.2 数值范围的魔法效应
-- 创建测试环境
CREATE TABLE sensor_data (
id SERIAL PRIMARY KEY,
sensor_id INT NOT NULL,
record_time TIMESTAMPTZ NOT NULL,
temperature NUMERIC(5,2)
);
INSERT INTO sensor_data (sensor_id, record_time, temperature)
SELECT n%100,
now() - (n || ' hours')::INTERVAL,
(RANDOM()*100)::NUMERIC(5,2)
FROM generate_series(1,1000000) n;
-- PL/pgSQL函数示例
CREATE OR REPLACE FUNCTION get_sensor_records(sensor INT, days INT)
RETURNS SETOF sensor_data AS $$
BEGIN
RETURN QUERY
SELECT *
FROM sensor_data
WHERE sensor_id = sensor -- PL变量
AND record_time > now() - (days || ' days')::INTERVAL;
END;
$$ LANGUAGE plpgsql;
-- 对比参数化查询
PREPARE sensor_query (INT, INT) AS
SELECT *
FROM sensor_data
WHERE sensor_id = $1
AND record_time > now() - ($2 || ' days')::INTERVAL;
当首次调用函数时使用days=1（近期数据量少），即使后续调用days=365（全年数据），执行计划仍可能选择索引扫描而不是全表扫描，因为规划器认为时间条件的筛选率仍然很高。
3. 性能显微镜：技术细节深度解析
3.1 计划缓存的运作机制
当使用PL/pgSQL变量时，执行计划的缓存钥匙包含：
- 函数对象ID
- 参数类型列表
- 但不包含参数具体值
而参数化查询的计划缓存钥匙则包含：
- 查询语句哈希值
- 参数类型列表
- 以及部分统计信息（当开启plan_cache_mode=auto时）
3.2 数据类型转换暗礁
-- 隐藏的类型转换陷阱示例
CREATE FUNCTION find_products(search_term VARCHAR)
RETURNS SETOF products AS $$
DECLARE
pattern TEXT := '%' || search_term || '%'; -- 隐式转换
BEGIN
RETURN QUERY
SELECT *
FROM products
WHERE description ILIKE pattern;
END;
$$ LANGUAGE plpgsql;
-- 改进方案：显式转换
CREATE FUNCTION find_products_v2(search_term TEXT)
RETURNS SETOF products AS $$
DECLARE
pattern TEXT := '%' || search_term || '%';
BEGIN
RETURN QUERY
SELECT *
FROM products
WHERE description ILIKE pattern::TEXT; -- 确保类型一致
END;
$$ LANGUAGE plpgsql;
当传入参数类型与变量定义类型不匹配时（比如传入CHAR类型参数给VARCHAR变量），可能导致执行时产生隐式转换，不仅消耗额外资源，还会破坏索引使用条件。
4. 战场指南：生产环境的生存法则
4.1 必须使用PL变量的场景
- 需要多次复用的计算逻辑
CREATE FUNCTION calculate_tax(order_id INT)
RETURNS NUMERIC AS $$
DECLARE
base_amount NUMERIC;
tax_rate NUMERIC;
BEGIN
SELECT total_amount
INTO base_amount
FROM orders
WHERE id = order_id;
SELECT rate
INTO tax_rate
FROM tax_table
WHERE amount_range @> base_amount;
RETURN base_amount * tax_rate; -- 两次计算使用同一变量
END;
$$ LANGUAGE plpgsql;
- 需要保持事务一致性的操作
CREATE PROCEDURE transfer_funds(
from_account INT,
to_account INT,
amount NUMERIC
) AS $$
DECLARE
from_balance NUMERIC;
BEGIN
SELECT balance
INTO from_balance
FROM accounts
WHERE id = from_account
FOR UPDATE;
IF from_balance < amount THEN
RAISE EXCEPTION '余额不足';
END IF;
UPDATE accounts
SET balance = balance - amount
WHERE id = from_account;
UPDATE accounts
SET balance = balance + amount
WHERE id = to_account;
END;
$$ LANGUAGE plpgsql;
4.2 应该采用SQL参数的场景
- WHERE条件中的筛选值
-- 动态SQL的示例
CREATE FUNCTION search_products(
category_id INT DEFAULT NULL,
min_price NUMERIC DEFAULT 0,
max_price NUMERIC DEFAULT 1000000
) RETURNS SETOF products AS $$
BEGIN
RETURN QUERY EXECUTE '
SELECT * FROM products
WHERE ($1 IS NULL OR category_id = $1)
AND price BETWEEN $2 AND $3'
USING category_id, min_price, max_price;
END;
$$ LANGUAGE plpgsql;
- 分页查询中的偏移量
CREATE FUNCTION get_paged_orders(
page_num INT,
page_size INT
) RETURNS SETOF orders AS $$
BEGIN
RETURN QUERY EXECUTE '
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT $1 OFFSET $2'
USING page_size, (page_num - 1) * page_size;
END;
$$ LANGUAGE plpgsql;
5. 终局启示录：最佳实践总结
5.1 决策流程图
- 需要复用中间计算结果 → PL变量
- 需要维护中间状态 → PL变量
- WHERE条件中接收外部输入 → SQL参数
- 需要利用参数嗅探优化 → SQL参数
- 查询条件存在多种模式 → 动态SQL+参数
5.2 性能优化锦囊
- 在事务块中重用函数调用
BEGIN;
SELECT * FROM get_user_orders(123) WHERE total_amount > 1000;
SELECT * FROM get_user_orders(123) WHERE status = 'pending';
COMMIT;
- 强制重新生成执行计划
-- 对于参数化查询
DEALLOCATE sensor_query;
EXECUTE sensor_query(456);
-- 在PL/pgSQL函数中
EXECUTE '...' USING params; -- 动态SQL每次都会重新计划
评论