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;
```

强制指定 GUC 指定query在执行时候的 GUC，只影响本次查询

/*+ SET statement_timeout='1ms' */
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;

强制设置 GUC

在某些情况下，我们希望只针对特定 query 设置 GUC，来做到精细化控制。比如我们可以针对某个 query 设置比较短的timeout，来做到类似黑名单的效果。

-- 强制设置1ms的timeout超时
/*+ set(statement_timeout '1ms') */  
SELECT u.user_id, u.name, o.order_id, o.amount, p1.product_name, p1.price
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN products p1 ON o.product_id = p1.product_id
JOIN products p2 ON o.product_id = p2.product_id
WHERE u.age > 30 AND o.amount > 500 AND p1.price > 50;

配置 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;
 
-- 重新连接数据库，然后确认生效，显示 on 表示生效
show compute_query_id;

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 VERBOSE）：

EXPLAIN VERBOSE 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;

屏蔽特定 query

执行目标查询（带EXPLAIN VERBOSE）：

EXPLAIN VERBOSE 
SELECT u.user_id, u.name, o.order_id, o.amount, p1.product_name, p1.price
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN products p1 ON o.product_id = p1.product_id
JOIN products p2 ON o.product_id = p2.product_id
WHERE u.age > 30 AND o.amount > 500 AND p1.price > 50
;

查看查询计划中的query_id：

 Query Identifier: 7764055527031527465

步骤2：将 hint 插入 hint_table

INSERT INTO hint_plan.hints (query_id, application_name, hints) 
VALUES ('7764055527031527465', '', 'set(statement_timeout 1ms)');

步骤3：验证提示效果

再次执行查询，确认被1ms的限制拦截导致超时。

test=> SELECT u.user_id, u.name, o.order_id, o.amount, p1.product_name, p1.price
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN products p1 ON o.product_id = p1.product_id
JOIN products p2 ON o.product_id = p2.product_id
WHERE u.age > 30 AND o.amount > 500 AND p1.price > 50;
ERROR:  canceling statement due to statement timeout
时间：3.771 ms

查看已注册提示

SELECT * FROM hint_plan.hints;

注意事项

功能支持限制：某些复杂查询，Hints 可能会被优化器忽略。需多次验证最终执行计划（EXPLAIN）。
语法正确性：Hints 必须严格遵循扩展语法，否则会被忽略。
统计信息更新：确保表的统计信息是最新状态，否则可能导致错误的计划。
小心过度优化：Hints 仅适用于优化器无法提供合适执行计划的场景，优先依赖 ProtonBase 原生统计模型。