SQL Hints
SQL Hints 机制允许用户在注释中添加 提示(Hint) 的方式影响 SQL 的具体执行,实现自定义的执行计划,ProtonBase 通过 pg_hint_plan
扩展支持这个能力。它允许开发者在某些情况下指导优化器生成特定的计划,从而优化性能。这在默认优化器可能无法做出最优决策时特别有用,例如统计信息不足或复杂查询场景下。
使用场景
pg_hint_plan
的主要使用场景包括:
- 复杂查询优化:处理JOIN、多表查询及索引干预。
- 静态计划比较:在性能调试中强制生成固定的计划以进行对比测试。
- 规避优化缺陷:当优化器未充分利用索引、高效路径等问题出现时,通过指定 hint 修正错误。
- 压力测试/实验室调优:测试不同执行计划对整体性能的影响。
加载扩展
加载扩展 通过系统管理员权限执行加载扩展的命令,更多有关该扩展的介绍,参考pg_hint_plan:
CREATE EXTENSION pg_hint_plan;
验证安装 执行以下查询,检查扩展是否被正确加载:
SELECT * FROM pg_extension WHERE extname = 'pg_hint_plan';
基本用法
pg_hint_plan
是通过查询的注释部分来插入 hints 的,如果使用了表的别名,参数部分需要替换为别名,其语法如下:
/*+ hint_name([t_alias] options) */
SELECT * FROM table_name t_alias WHERE ...
Hint 类型
-
Scan Method 操作控制 指定查询使用何种扫描方式,当前支持
SeqScan
、IndexScan
。/*+ SeqScan(t_alias) */ SELECT * FROM table_name t_alias WHERE col = 100; /*+ IndexScan(t_alias index_name) */ SELECT * FROM table_name t_alias WHERE col = 100;
-
Join Method 操作控制 提供 Join 操作的执行顺序和算法,当前支持
NestLoop
、HashJoin
、MergeJoin
。/*+ HashJoin(table1 table2) */ SELECT * FROM table1 JOIN table2 USING (id); /*+ NestLoop(table1 table2) */ SELECT * FROM table1 JOIN table2 USING (id);
-
强制Join Order计划顺序 重写操作的执行顺序,Join 的两边用括号组合,多个表的 Join,用过多组括号组合成一颗树形结构。
/*+ Leading(((table1 table2) table3)) */ SELECT * FROM table1 JOIN table2 ON table1.id = table2.id JOIN table3 ON table2.id = table3.id;
常用操作
表结构和数据准备
创建示例表,我们创建三个表:users
(用户信息)、orders
(订单信息)和 products
(商品信息),并为它们添加必要的索引。
-- 创建 users 表
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INT NOT NULL
);
-- 创建 orders 表
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id INT NOT NULL,
product_id INT NOT NULL,
order_date DATE NOT NULL,
amount NUMERIC(10, 2) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
-- 创建 products 表
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name TEXT NOT NULL,
price NUMERIC(10, 2) NOT NULL
);
-- 为 orders 表的 user_id 和 product_id 列创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_product_id ON orders(product_id);
插入一些测试数据以便验证查询效果。
-- 插入 users 表数据
INSERT INTO users (name, age)
SELECT 'User_' || i, 20 + (i % 30)
FROM generate_series(1, 1000) AS i;
-- 插入 products 表数据
INSERT INTO products (product_name, price)
SELECT 'Product_' || i, (random() * 100)::NUMERIC(10, 2)
FROM generate_series(1, 100) AS i;
-- 插入 orders 表数据
INSERT INTO orders (user_id, product_id, order_date, amount)
SELECT (i % 1000) + 1, (i % 100) + 1, CURRENT_DATE - (i % 365), (random() * 1000)::NUMERIC(10, 2)
FROM generate_series(1, 10000) AS i;
强制使用索引扫描
假设我们希望查询某个用户的所有订单,优化器可能会选择全表扫描,但我们希望强制使用索引扫描。
-- 强制使用索引扫描
/*+ IndexScan(orders idx_orders_user_id) */
SELECT *
FROM orders
WHERE user_id = 42;
验证执行计划,使用 EXPLAIN
查看执行计划,确认是否使用了索引扫描:
/*+ IndexScan(orders idx_orders_user_id) */
EXPLAIN
SELECT *
FROM orders
WHERE user_id = 42;
调整 JOIN 顺序
假设我们需要查询用户及其订单信息,默认的优化器可能选择次优的 JOIN 顺序。我们可以通过 Leading
hint 指定 JOIN 顺序。
-- 查询年龄大于 30 的用户,且订单金额大于 500 的订单,以及这些订单对应的商品信息。
-- 强制 JOIN 顺序
/*+ Leading(((u o) p)) */
SELECT u.user_id, u.name, o.order_id, o.amount, p.product_name, p.price
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN products p ON o.product_id = p.product_id
WHERE u.age > 30 AND o.amount > 500 AND p.price > 50;
验证执行计划
/*+ Leading(((u o) p)) */
EXPLAIN
SELECT u.user_id, u.name, o.order_id, o.amount, p.product_name, p.price
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN products p ON o.product_id = p.product_id
WHERE u.age > 30 AND o.amount > 500 AND p.price > 50;
假设希望优化查询性能,尝试将 products
表放在 JOIN 顺序的最前面。
-- 调整 JOIN 顺序
/*+ Leading((u (p o))) */
SELECT u.user_id, u.name, o.order_id, o.amount, p.product_name, p.price
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN products p ON o.product_id = p.product_id
WHERE u.age > 30 AND o.amount > 500 AND p.price > 50;
验证执行计划
/*+ Leading((u (p o))) */
EXPLAIN
SELECT u.user_id, u.name, o.order_id, o.amount, p.product_name, p.price
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN products p ON o.product_id = p.product_id
WHERE u.age > 30 AND o.amount > 500 AND p.price > 50;
强制使用 Nested Loop Join
在某些情况下,我们可能希望强制使用嵌套循环(Nested Loop
)来执行 JOIN。
-- 强制使用嵌套循环
/*+ NestLoop(u o p) */
SELECT u.user_id, u.name, o.order_id, o.amount, p.product_name, p.price
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN products p ON o.product_id = p.product_id
WHERE u.age > 30 AND o.amount > 500 AND p.price > 50;
验证执行计划
/*+ NestLoop(u o p) */
EXPLAIN
SELECT u.user_id, u.name, o.order_id, o.amount, p.product_name, p.price
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN products p ON o.product_id = p.product_id
WHERE u.age > 30 AND o.amount > 500 AND p.price > 50;
强制使用 Hash Join
假设我们需要查询用户及其订单信息,优化器可能选择嵌套循环(Nested Loop
),但我们希望强制使用 Hash Join
。
-- 强制使用 Hash Join
/*+ HashJoin(u o p) */
SELECT u.user_id, u.name, o.order_id, o.amount, p.product_name, p.price
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN products p ON o.product_id = p.product_id
WHERE u.age > 30 AND o.amount > 500 AND p.price > 50;
验证执行计划
/*+ HashJoin(u o p) */
EXPLAIN
SELECT u.user_id, u.name, o.order_id, o.amount, p.product_name, p.price
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN products p ON o.product_id = p.product_id
WHERE u.age > 30 AND o.amount > 500 AND p.price > 50;
强制顺序扫描
在某些情况下,优化器可能选择索引扫描,但我们希望强制使用顺序扫描(SeqScan
)。
-- 强制使用顺序扫描
/*+ SeqScan(orders) */
SELECT *
FROM orders
WHERE amount > 500;
验证执行计划
/*+ SeqScan(orders) */
EXPLAIN
SELECT *
FROM orders
WHERE amount > 500;
配置 pg_hint_plan.hint_table
hint table 功能可以动态地为特定类型的查询设置优化器提示。
启用必要参数
-- 启用查询ID计算,按需选择 Session 级别还是 Database 级别开启
SET compute_query_id = on;
-- 启用hint table功能
ALTER DATABASE dbname SET pg_hint_plan.enable_hint_table = on;
-- 重新加载配置
SELECT pg_reload_conf();
hint_table 数据结构
hint_table 结构如下,通过向表中注入规则,可以动态调整 pg_hint 行为:
CREATE TABLE hint_plan.hints (
id serial PRIMARY KEY,
query_id text NOT NULL, -- 查询的唯一哈希标识
application_name text NOT NULL, -- 应用程序名称过滤,如果没有可以设置空字符串
hints text NOT NULL -- Hint 内容
UNIQUE (query_id, application_name)
);
使用流程
步骤1:获取查询的query_id
- 执行目标查询(带EXPLAIN):
EXPLAIN SELECT * FROM users WHERE user_id = 100;
- 查看查询计划中的query_id:
Seq Scan on users (cost=0.00..25.88 rows=6 width=36)
Filter: (user_id = 100)
Query Identifier: -1234567890123456789 -- 这就是query_id
步骤2:将 hint 插入 hint_table
INSERT INTO hint_plan.hints (query_id, application_name, hints)
VALUES ('-1234567890123456789', 'app1', 'IndexScan(users)');
步骤3:验证提示效果
再次执行查询查看执行计划,应显示使用了索引扫描。:
EXPLAIN SELECT * FROM users WHERE user_id = 100;
查看已注册提示
SELECT * FROM hint_plan.hints;
注意事项
- 功能支持限制:某些复杂查询,Hints 可能会被优化器忽略。需多次验证最终执行计划(
EXPLAIN
)。 - 语法正确性:Hints 必须严格遵循扩展语法,否则会被忽略。
- 统计信息更新:确保表的统计信息是最新状态,否则可能导致错误的计划。
- 小心过度优化:Hints 仅适用于优化器无法提供合适执行计划的场景,优先依赖 ProtonBase 原生统计模型。