Use hints to control SQL execution for optimization - Hologres

Hints let you override Hologres's query optimizer decisions — join order, join method, data distribution, and GUC parameters — without changing the query logic. Use hints when the optimizer produces a suboptimal execution plan due to stale statistics or unusual data distribution.

Hints are supported in Hologres V2.2 and later. If your instance runs V2.1 or earlier, upgrade before using hints.

Prerequisites

Before you begin, ensure that you have:

A Hologres instance running V2.2 or later
The pg_hint_plan_enable_hint GUC parameter set to on

Enable hints at the session level:

SET pg_hint_plan_enable_hint=on;

Or enable hints at the database level (takes effect for new connections):

ALTER DATABASE <dbname> SET pg_hint_plan_enable_hint=on;

Hint syntax

Place a hint immediately after the DML keyword (SELECT, INSERT, UPDATE, or DELETE):

SELECT|UPDATE|INSERT|DELETE /*+HINT <HintName(params)> */ ...

Key rules:

Enclose hints between /*+HINT and */.
Hint keywords are case-insensitive.
A single hint block can contain multiple hint keywords.
Comments are not allowed inside a hint block.
If a SELECT statement has multiple consecutive hint blocks, only the first takes effect.

Hint keywords

The following table lists all supported hint keywords.

Type	Keyword	Description	Version
Join method	`HashJoin(table1 table2[ table...])`	Force a hash join.	V2.2+
	`NestLoop(table1 table2[ table...])`	Force a nested loop join.	V2.2+
Join order	`Leading(table1 table2[ table...])`	Force a join order (left-deep tree by default).	V2.2+
	`Leading(<join pair>)`	Force a join order and direction. Use parentheses `()` to specify join pairs explicitly.	V2.2+
Runtime filter	`RuntimeFilter(table1 table2[ table...])`	Force a runtime filter for hash joins on the specified tables.	V2.2+
GUC	`Set(GUC-parameter value)`	Set a GUC parameter value for the duration of the current query. See GUC parameters.	V2.2+
Motion	`Broadcast(table[ table...])`	Force the specified table to be broadcast in a JOIN operation. Use with `Leading` in most cases.	V3.0+
	`NoBroadcast(table[ table...])`	Force the specified table not to be broadcast.	V3.0+
	`Gather(table[ table...])`	Force the specified table to be gathered in a JOIN operation.	V2.2+
	`NoGather(table[ table...])`	Force the specified table not to be gathered.	V2.2+

Examples:

-- HashJoin
SELECT
    /*+HINT HashJoin(table1 table2 table3) */
    table1.a
FROM
    table1
    JOIN table2 ON table1.a = table2.a
    JOIN table3 ON table1.a = table3.a;

-- NestLoop
SELECT /*+HINT NestLoop(table1 table2) */ * FROM table1 JOIN table2 ON table1.a = table2.a;

-- Leading (left-deep: join table2 and table1 first, then table3)
SELECT /*+HINT Leading(table2 table1) */ table1.a FROM table1 JOIN table2 ON table1.a = table2.a;

-- Leading with join pairs (parentheses control tree shape)
-- This joins (table2 JOIN table1) and (table3 JOIN table4) independently, then joins the two results.
SELECT
/*+HINT Leading((table2 table1) (table3 table4)) */
    *
FROM
    table1
    LEFT JOIN table2 ON table1.a = table2.a
    RIGHT JOIN table3 ON table1.a = table3.a
    LEFT JOIN table4 ON table3.a = table4.a
ORDER BY
    table1.a;

-- RuntimeFilter
SELECT /*+HINT RuntimeFilter(table1 table2) */ * FROM table1 JOIN table2 ON table1.a = table2.a;

-- GUC: disable count distinct reuse for this query only
EXPLAIN
SELECT
    /*+HINT set(hg_experimental_enable_reuse_cte_of_count_distinct off) */
    count(DISTINCT a),
    count(DISTINCT b)
FROM table1;

Usage notes

Supported objects

Hints apply to regular tables (including foreign tables), subqueries, and common table expressions (CTEs). Views are not supported.

Hint scope

A hint applies only to the query level where it is placed. A hint in a parent query cannot reference tables in a subquery, and vice versa. In the following example, Leading(tt t2) can only reference tt and t2; Leading(t t1) can only reference t and t1.

SELECT /*+HINT Leading(t t1) */ * FROM t1 JOIN (
  SELECT /*+HINT Leading(tt t2) */ * FROM t2 JOIN (
    SELECT * FROM t3
  ) tt
) t;

Exception — GUC hints: A GUC hint takes effect for the entire query, even if placed in a subquery. Subsequent queries are not affected.

-- The GUC hint in the subquery applies to the entire outer query.
SELECT
    count(DISTINCT a),
    count(DISTINCT b)
FROM (
    SELECT
        /*+HINT set(hg_experimental_enable_reuse_cte_of_count_distinct off) */
        t1.a, t2.b
    FROM
        t1
        JOIN t2 ON t1.a = t2.a)

Leading hint: controlling join tree shape

Without parentheses, Leading builds a left-deep tree: Leading(t1 t2 t3) joins t1 and t2 first, then the result with t3.

Use parentheses to explicitly control the tree shape:

Syntax	Tree shape	Join order
`Leading(t1 t2 t3)`	Left-deep	(t1 ⋈ t2) ⋈ t3
`Leading(t1 (t2 t3))`	Right-deep	t1 ⋈ (t2 ⋈ t3)

Nested parentheses are supported.

Conflict resolution

When hints of the same type conflict, the first hint takes effect. Conflicts occur when:

Two hints specify the same tables.
Two hints specify identical table sets.
The tables in one join order hint are a subset of the tables in another.
The table sets in two join method, runtime filter, or skew join hints are not subsets of each other.

Examples:

-- HashJoin(t1 t2) and NestLoop(t2 t1) conflict (same tables). Only HashJoin(t1 t2) is applied.
SELECT /*+HINT HashJoin(t1 t2) NestLoop(t2 t1) */ ...

-- Leading(t1 t2) is a subset of Leading(t1 t2 t3). Only Leading(t1 t2) is applied.
SELECT /*+HINT Leading(t1 t2) Leading(t1 t2 t3) */ ...

Join method and runtime filter hints

At least two valid parameters are required. Valid parameters are tables, subqueries, or CTEs at the query level where the hint applies. For example, Leading(t1 t1) has only one valid parameter.
If the generated candidate plan does not include the join specified by a join method hint, the hint has no effect. For example, HashJoin(t1 t2) has no effect if the plan joins t1 with t3 first and then joins t2. In this case, add Leading(t1 t2) to force the join order.
Runtime filter hints take effect only on hash joins.

INSERT INTO ... SELECT

The INSERT hint applies to the destination table and the outermost SELECT source table. You cannot specify hints on both the INSERT and SELECT clauses simultaneously:

-- Correct: hint on INSERT
INSERT /*+HINT Leading(target (t1 t2)) */ INTO target SELECT t1.a, t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

-- Correct: hint on SELECT
INSERT INTO target SELECT /*+HINT Leading(t2 t1) */ t1.a, t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

-- Incorrect: hints on both INSERT and SELECT — results in an error
INSERT /*+HINT Leading(target (t1 t2)) */ INTO target SELECT /*+HINT Leading(t2 t1) */ t1.a, t2.b FROM t1 JOIN t2 ON t1.a=t2.a;
-- ERROR: insert statement with hint should not have sub select with hint at the same time

Scenarios

The following examples use these tables:

CREATE TABLE target (a int primary key, b int);
CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (a int, b int);
CREATE TABLE t3 (a int);
CREATE TABLE t4 (a int);

Adjust join order

Stale statistics are the most common reason for a suboptimal join order. This happens when the ANALYZE statement has not been run recently, or when the actual row count after a filter or join differs significantly from the estimate. See ANALYZE and AUTO ANALYZE.

In a hash join, Hologres uses the smaller table to build the hash table (shown at the bottom of the Hash operator in the execution plan). If t2 has more rows than t1 but the optimizer chooses t1 as the build side, use a Leading hint to swap the join order.

SELECT /*+HINT Leading(t2 t1) */ t1.a FROM t1 JOIN t2 ON t1.a = t2.a;

Execution plan without hint:

QUERY PLAN
-----------------------------------------------------------------------------------
 Gather  (cost=0.00..10.07 rows=1000 width=4)
   ->  Hash Join  (cost=0.00..10.05 rows=1000 width=4)
         Hash Cond: (t1.a = t2.a)
         ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
               Hash Key: t1.a
               ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                     ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)
         ->  Hash  (cost=5.01..5.01 rows=1000 width=4)
               ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                     Hash Key: t2.a
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                           ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=4)

Execution plan with hint:

QUERY PLAN
-----------------------------------------------------------------------------------
 Gather  (cost=0.00..10.07 rows=1000 width=4)
   ->  Hash Join  (cost=0.00..10.05 rows=1000 width=4)
         Hash Cond: (t2.a = t1.a)
         ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
               Hash Key: t2.a
               ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                     ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=4)
         ->  Hash  (cost=5.01..5.01 rows=1000 width=4)
               ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                     Hash Key: t1.a
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                           ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)

t2 now builds the hash table, which is the intended behavior when t2 is smaller.

Apply a GUC hint

GUC hints configure a GUC parameter for a single query. The parameter reverts after the query completes and does not affect other queries.

SELECT /*+HINT set(hg_experimental_query_batch_size 512) */ t1.a FROM t1 JOIN t2 ON t1.a = t2.a;

Execution plan:

QUERY PLAN
Hash Join  (cost=0.00..10.00 rows=1 width=4)
  Hash Cond: (t1.a = t2.a)
  ->  Gather  (cost=0.00..5.00 rows=1 width=4)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
              ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=4)
  ->  Hash  (cost=5.00..5.00 rows=1 width=4)
        ->  Gather  (cost=0.00..5.00 rows=1 width=4)
              ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                    ->  Seq Scan on t2  (cost=0.00..5.00 rows=1 width=4)

Use hints in CTEs and subqueries

Hints in CTEs and subqueries each control the join strategy at their own query level. The following example applies multiple hints across different query levels.

WITH c1 AS (
        SELECT /*+HINT Leading(t2 t1) */ t1.a FROM (
            (
                SELECT /*+HINT leading(t2 t1) */ t1.a FROM t1 JOIN t2 ON t1.a = t2.a
            ) AS t1
            JOIN
            (
                SELECT /*+HINT NestLoop(t4 t3) */ t4.a FROM t3 JOIN t4 ON t3.a = t4.a
            ) AS t2
            ON t1.a = t2.a
        )
    ),
    c2 AS (
        SELECT /*+HINT leading(t1 t2) */ t2.a FROM (
            (
                SELECT /*+HINT Leading(t1 t2) */ t1.a FROM t1 JOIN t2 ON t1.a = t2.a
            ) AS t1
            JOIN
            (
                SELECT /*+HINT Leading(t4 t3) */ t4.a FROM t3 JOIN t4 ON t3.a = t4.a
            ) AS t2
            ON t1.a = t2.a
        )
    )
    SELECT /*+HINT NestLoop(v2 v1) */  * FROM (
        (
            SELECT /*+HINT Leading (c1 t2) */ c1.a FROM c1 JOIN t2 ON c1.a = t2.a
        ) AS v1
        JOIN
        (
            SELECT /*+HINT Leading (t1 c2) */ c2.a FROM t1 JOIN c2 ON t1.a = c2.a
        ) AS v2
        ON v1.a = v2.a
    )
    ORDER BY v2.a;

Use hints in INSERT statements

Use INSERT hints when the destination table participates in a join with source tables and the default join order is suboptimal.

Example 1: Hint on the INSERT clause

The hint applies to the destination table and the outermost SELECT source table. Use this when the t1 JOIN t2 result is small but the destination table target is large.

INSERT /*+HINT Leading(target (t1 t2)) */ INTO target SELECT t1.a, t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

Execution plan:

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Gather  (cost=0.00..26.57 rows=1000 width=8)
   ->  Insert  (cost=0.00..26.54 rows=1000 width=8)
         ->  Project  (cost=0.00..16.12 rows=1000 width=8)
               ->  Hash Right Join  (cost=0.00..15.12 rows=1000 width=12)
                     Hash Cond: (target.a = t1.a)
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                           ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=4)
                     ->  Hash  (cost=10.07..10.07 rows=1000 width=8)
                           ->  Redistribution  (cost=0.00..10.07 rows=1000 width=8)
                                 Hash Key: t1.a
                                 ->  Hash Join  (cost=0.00..10.06 rows=1000 width=8)
                                       Hash Cond: (t1.a = t2.a)
                                       ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                                             Hash Key: t1.a
                                             ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                                                   ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)
                                       ->  Hash  (cost=5.01..5.01 rows=1000 width=8)
                                             ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                                                   Hash Key: t2.a
                                                   ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                                         ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)

Example 2: Hint on the SELECT clause

The following two statements produce the same effect:

INSERT INTO target SELECT /*+HINT Leading(t2 t1) */ t1.a, t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

INSERT /*+HINT Leading(t2 t1) */ INTO target SELECT t1.a, t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

Use hints in UPDATE statements

Use UPDATE hints when the destination table joins with a source table and you need to control the join order.

In the following example, t1 has more rows than target. The hint makes target the build side of the hash join.

UPDATE /*+HINT Leading(t1 target) */ target SET b=t1.b+1 FROM t1 WHERE t1.a=target.a;

Execution plan without hint:

QUERY PLAN
-----------------------------------------------------------------------------------------------
 Gather  (cost=0.00..52.77 rows=1000 width=1)
   ->  Update  (cost=0.00..52.76 rows=1000 width=1)
         ->  Project  (cost=0.00..11.09 rows=1000 width=32)
               ->  Hash Join  (cost=0.00..10.08 rows=1000 width=32)
                     Hash Cond: (target.a = t1.a)
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=28)
                           ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=28)
                     ->  Hash  (cost=5.01..5.01 rows=1000 width=8)
                           ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                                 Hash Key: t1.a
                                 ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                       ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)

Execution plan with hint:

QUERY PLAN
----------------------------------------------------------------------------------------------
 Gather  (cost=0.00..52.77 rows=1000 width=1)
   ->  Update  (cost=0.00..52.76 rows=1000 width=1)
         ->  Project  (cost=0.00..11.09 rows=1000 width=32)
               ->  Hash Join  (cost=0.00..10.08 rows=1000 width=32)
                     Hash Cond: (t1.a = target.a)
                     ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                           Hash Key: t1.a
                           ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                 ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)
                     ->  Hash  (cost=5.00..5.00 rows=1000 width=28)
                           ->  Local Gather  (cost=0.00..5.00 rows=1000 width=28)
                                 ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=28)

target now builds the hash table, reducing the cost of the join.

Use runtime filter hints

Hologres supports runtime filters for hash joins. If a query does not meet the conditions for triggering a runtime filter automatically, use a RuntimeFilter hint to force one.

Important

Forcing a runtime filter does not always improve performance. Verify the execution plan before applying this hint in production.

SELECT /*+HINT runtimefilter(t1 t2) */ * FROM t1 JOIN t2 ON t1.a = t2.a;

Execution plan (the Runtime Filter Cond line confirms the filter is active):

QUERY PLAN
-----------------------------------------------------------------------------------
 Gather  (cost=0.00..10.13 rows=1000 width=16)
   ->  Hash Join  (cost=0.00..10.07 rows=1000 width=16)
         Hash Cond: (t1.a = t2.a)
         Runtime Filter Cond: (t1.a = t2.a)
         ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
               Hash Key: t1.a
               ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                     ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)
                           Runtime Filter Target Expr: t1.a
         ->  Hash  (cost=5.01..5.01 rows=1000 width=8)
               ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                     Hash Key: t2.a
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                           ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)

Use motion hints

Motion hints control how Hologres distributes data between workers during a JOIN — either by broadcasting a table to all workers or by redistributing (shuffling) it.

Example 1: Force broadcast

Use Broadcast when statistics are inaccurate and the optimizer incorrectly shuffles a large amount of data. Because a hash join can only broadcast on the build side, pair Broadcast with Leading to specify the correct join order.

SELECT /*+HINT Leading(t2 t1) Broadcast(t1) */ * FROM t1 JOIN t2 ON t1.a = t2.a;

Execution plan:

QUERY PLAN
----------------------------------------------------------------------------------------------
  Gather  (cost=0.00..100000000000000005366162204393472.00 rows=1000 width=16)
   ->  Hash Join  (cost=0.00..100000000000000005366162204393472.00 rows=1000 width=16)
         Hash Cond: (t2.a = t1.a)
         ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
               ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)
         ->  Hash  (cost=100000000000000005366162204393472.00..100000000000000005366162204393472.00 rows=3000 width=8)
               ->  Broadcast  (cost=0.00..100000000000000005366162204393472.00 rows=3000 width=8)
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                           ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)

Example 2: Disable broadcast

Use NoBroadcast when the optimizer underestimates the actual row count of a table and attempts to broadcast a large table, leading to very poor performance. This hint requires Hologres V3.0 or later.

First, create the test tables:

Sample data

CREATE TABLE test1(a int, b int);
CREATE TABLE test2(a int, b int);

INSERT INTO test1 SELECT 1, i FROM generate_series(1, 10) AS i;
INSERT INTO test2 SELECT 1, i FROM generate_series(1, 1000000) AS i;

ANALYZE test1, test2;

Sample SQL statements

Without the hint, the optimizer broadcasts test1 (estimated at 200 rows after stats, but actually only 10):
Perform broadcast without using a hint
```
EXPLAIN SELECT * FROM test1 JOIN test2 ON test1.b = test2.b;
```
Use a hint to disable broadcast
```
EXPLAIN SELECT /*+HINT NoBroadcast(test1) */ * FROM test1 JOIN test2 ON test1.b = test2.b;
```

Execution plan:

With NoBroadcast, Hologres redistributes both tables instead:

Perform broadcast without using a hint

QUERY PLAN
---------------------------------------------------------------------------------
Gather  (cost=0.00..51.98 rows=1000000 width=16)
  ->  Hash Join  (cost=0.00..13.12 rows=1000000 width=16)
        Hash Cond: (test2.b = test1.b)
        ->  Local Gather  (cost=0.00..5.23 rows=1000000 width=8)
              ->  Seq Scan on test2  (cost=0.00..5.20 rows=1000000 width=8)
        ->  Hash  (cost=5.00..5.00 rows=200 width=8)
              ->  Broadcast  (cost=0.00..5.00 rows=200 width=8)
                    ->  Local Gather  (cost=0.00..5.00 rows=10 width=8)
                          ->  Seq Scan on test1  (cost=0.00..5.00 rows=10 width=8)

Use a hint to disable broadcast

QUERY PLAN
---------------------------------------------------------------------------------
Gather  (cost=0.00..53.23 rows=1000000 width=16)
  ->  Hash Join  (cost=0.00..14.37 rows=1000000 width=16)
        Hash Cond: (test2.b = test1.b)
        ->  Redistribution  (cost=0.00..6.48 rows=1000000 width=8)
              Hash Key: test2.b
              ->  Local Gather  (cost=0.00..5.23 rows=1000000 width=8)
                    ->  Seq Scan on test2  (cost=0.00..5.20 rows=1000000 width=8)
        ->  Hash  (cost=5.00..5.00 rows=10 width=8)
              ->  Redistribution  (cost=0.00..5.00 rows=10 width=8)
                    Hash Key: test1.b
                    ->  Local Gather  (cost=0.00..5.00 rows=10 width=8)
                          ->  Seq Scan on test1  (cost=0.00..5.00 rows=10 width=8)

Verify that hints take effect

Run EXPLAIN before and after adding a hint. Confirm the change in execution plan matches your intent — for example, the Hash Cond order changes when you swap join order with Leading, or Runtime Filter Cond appears when you apply RuntimeFilter.

If the plan does not change, check the following:

Hint not applied due to conflict: When hints of the same type conflict, only the first is parsed. Remove duplicate or conflicting hints.
Hint parameters invalid: Join method and join order hints require at least two valid parameters (tables, subqueries, or CTEs at the same query level). Leading(t1 t1) has only one valid parameter and will not take effect.
Plan incompatible with hint: If the specified combination is impossible (for example, NestLoop with a RIGHT JOIN on certain table configurations), Hologres returns: ERROR: ORCA failed to produce a plan : No plan has been computed for required properties. Review the hint combination and adjust accordingly.

Hologres:HINT

Prerequisites

Hint syntax

Hint keywords

Usage notes

Supported objects

Hint scope

Leading hint: controlling join tree shape

Conflict resolution

Join method and runtime filter hints

INSERT INTO ... SELECT

Scenarios

Adjust join order

Apply a GUC hint

Use hints in CTEs and subqueries

Use hints in INSERT statements

Use hints in UPDATE statements

Use runtime filter hints

Use motion hints

Perform broadcast without using a hint

Use a hint to disable broadcast

Perform broadcast without using a hint

Use a hint to disable broadcast

Verify that hints take effect

What's next