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;

示例 1：强制使用索引扫描

假设我们希望查询某个用户的所有订单，优化器可能会选择全表扫描，但我们希望强制使用索引扫描。

-- 强制使用索引扫描
/*+ 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;

示例 2：调整 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;

示例 3：强制使用 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;

示例 4：强制使用 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;

示例 5：强制顺序扫描

在某些情况下，优化器可能选择索引扫描，但我们希望强制使用顺序扫描（SeqScan）。

-- 强制使用顺序扫描
/*+ SeqScan(orders) */
SELECT *
FROM orders
WHERE amount > 500;

验证执行计划

/*+ SeqScan(orders) */
EXPLAIN
SELECT *
FROM orders
WHERE amount > 500;

注意事项

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