GuidesQuery & Optimization

SQL Hints

The SQL Hints mechanism allows users to influence SQL execution by adding hints within comments, enabling custom execution plans. Tacnode supports this capability via the pg_hint_plan extension. This lets developers guide the optimizer to generate specific plans in certain scenarios, improving performance. Especially useful when the default optimizer cannot make optimal decisions, such as with insufficient statistics or complex queries.

Usage Scenarios

Primary use cases for pg_hint_plan:

  1. Complex Query Optimization: Handling JOINs, multi-table queries, and index intervention.
  2. Static Plan Comparison: Forcing fixed plans during performance debugging for comparative testing.
  3. Workaround for Optimizer Limitations: Correcting suboptimal plans when the optimizer fails to utilize indexes or efficient paths.
  4. Stress Testing/Lab Tuning: Testing the impact of different execution plans on overall performance.

Extension Loading

Load the extension Run the following command with superuser privileges. For more details, see pg_hint_plan:

CREATE EXTENSION pg_hint_plan;

Verify installation Run the following query to check if the extension is loaded:

SELECT * FROM pg_extension WHERE extname = 'pg_hint_plan';

Supported Hint Types

pg_hint_plan injects hints via SQL comments. If table aliases are used, parameters must match the alias. Syntax:

/*+ hint_name([t_alias] options) */
SELECT * FROM table_name t_alias WHERE ...

  1. Scan Method Control Specify scan method for queries. Supported: 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;

  2. Join Method Control Specify join order and algorithm. Supported: NestLoop, HashJoin, MergeJoin.

    /*+ HashJoin(table1 table2) */
SELECT * FROM table1 JOIN table2 USING (id);
 
/*+ NestLoop(table1 table2) */
SELECT * FROM table1 JOIN table2 USING (id);

  3. Force Join Order Override join order. Use parentheses to group join sides; for multiple tables, nest parentheses to form a tree structure.

    /*+ Leading(((table1 table2) table3)) */
SELECT * FROM table1
JOIN table2 ON table1.id = table2.id
JOIN table3 ON table2.id = table3.id;

  4. Set GUC mannually Set query runtime GUC, effective for the current query

    /*+ SET statement_timeout='1ms' */
SELECT * FROM table1
JOIN table2 ON table1.id = table2.id
JOIN table3 ON table2.id = table3.id;

Common Operations

Table structure and data preparation

Create sample tables: users (user info), orders (order info), and products (product info), with necessary indexes.

-- Create users table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL
);
 
-- Create orders table
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)
);
 
-- Create products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);
 
-- Create indexes on orders.user_id and orders.product_id
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_product_id ON orders(product_id);

Insert test data for query validation.

-- Insert data into users
INSERT INTO users (name, age)
SELECT 'User_' || i, 20 + (i % 30)
FROM generate_series(1, 1000) AS i;
 
-- Insert data into products
INSERT INTO products (product_name, price)
SELECT 'Product_' || i, (random() * 100)::NUMERIC(10, 2)
FROM generate_series(1, 100) AS i;
 
-- Insert data into 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;

Force Index Scan

To query all orders for a specific user, the optimizer may choose a full table scan. Force index scan as follows:

-- Force index scan
/*+ IndexScan(orders idx_orders_user_id) */
SELECT *
FROM orders
WHERE user_id = 42;

Verify execution plan using EXPLAIN:

/*+ IndexScan(orders idx_orders_user_id) */
EXPLAIN
SELECT *
FROM orders
WHERE user_id = 42;

Adjust JOIN Order

To query users and their orders, the optimizer may select a suboptimal join order. Use the Leading hint to specify join order.

-- Query users older than 30, orders with amount > 500, and related product info.
-- Force join order
/*+ 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;

Verify execution plan:

/*+ 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;

To optimize further, place products first in the join order.

-- Adjust join order
/*+ 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;

Verify execution plan:

/*+ 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;

Force Nested Loop Join

In some cases, force the use of nested loop join (Nested Loop):

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

Verify execution plan:

/*+ 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;

Force Hash Join

To force a hash join when the optimizer chooses nested loop:

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

Verify execution plan:

/*+ 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;

Force Sequential Scan

When the optimizer chooses index scan but sequential scan is preferred:

-- Force sequential scan
/*+ SeqScan(orders) */
SELECT *
FROM orders
WHERE amount > 500;

Verify execution plan:

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

Setting GUC Mannually

In certain scenarios, you may want to set GUC parameters specifically for a single query to exercise fine-grained control. For example, setting a much shorter timeout for a specific query acts as a kind of blacklist for that query.

-- Force a 1ms statement 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 Configuration

The hint table feature allows you to dynamically assign optimizer hints to specific types of queries.

Enable the required parameters

-- Enable query ID calculation; choose SESSION or DATABASE level as needed
SET compute_query_id = on;
 
-- Enable hint table functionality
ALTER  DATABASE dbname SET pg_hint_plan.enable_hint_table = on;
 
-- Reconnect to the database and verify that the setting is active. If the output shows "on", the parameter is in effect.
show compute_query_id;

hint_table Data Structure

The structure of the hint_table is as follows. By inserting rules into this table, you can dynamically adjust the behavior of pg_hint:

CREATE TABLE hint_plan.hints (
    id serial PRIMARY KEY,
    query_id text NOT NULL,          -- Unique hash identifier for the query
    application_name text NOT NULL,  -- Application name filter; set to an empty string if not used
    hints text NOT NULL              -- Hint content
    UNIQUE (query_id, application_name)
);

Examples

Change the query plan

Step 1: Obtain the query_id

  1. Execute the target query with EXPLAIN:

    EXPLAIN VERBOSE SELECT * FROM users WHERE user_id = 100;

  2. Retrieve the query_id from the plan output:

    Seq Scan on users (cost=0.00..25.88 rows=6 width=36)
  Filter: (user_id = 100)
Query Identifier: -1234567890123456789 -- this is the query_id

Step 2: Insert the hint into hint_table

INSERT INTO hint_plan.hints (query_id, application_name, hints)
VALUES ('-1234567890123456789', 'app1', 'IndexScan(users)');

Step 3: Verify hint effectiveness

Re-execute the query and review the execution plan; it should now show an index scan in use.

EXPLAIN SELECT * FROM users WHERE user_id = 100;

Block Specific Query

  1. Execute the target query with EXPLAIN VERBOSE to obtain its query ID:
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
;
  1. Retrieve the query_id from the query plan output:
 Query Identifier: 7764055527031527465

Step 2: Insert the hint into the hint_table

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

Step 3: Verify the effect of the hint

Re-run the query to confirm it is terminated with a 1ms timeout.

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
Duration: 3.771 ms

View Registered Hints

SELECT * FROM hint_plan.hints;

Notes

  1. Feature Support Limitations: For complex queries, hints may be ignored by the optimizer. Always verify the actual execution plan repeatedly via EXPLAIN.
  2. Hint Syntax Requirements: Hints must strictly follow the extension’s syntax; otherwise, they are ignored.
  3. Statistics Accuracy: Make sure the table's statistics are up-to-date to avoid generating suboptimal plans.
  4. Avoid Over-optimization: Use hints only when the optimizer cannot deliver an ideal plan; prioritize relying on the native Tacnode statistical model.

Previous

Query Tuning

Next

Operations

On this page