PolarDB:ePQ Hint Injection

How it works

ePQ Hint Injection relies on two components working together:

• `polar_sql_mapping` — intercepts matching queries and prepends a pg_hint_plan-compatible hint comment before the query reaches the planner.

• `pg_hint_plan` — reads the injected hint and adjusts the query execution plan.

Two mapping tables control the behavior:

Only inject hints for queries where the default optimizer behavior is confirmed to be suboptimal. Mapping large numbers of queries increases matching overhead.

Prerequisites

Before you begin, make sure that:

• pg_hint_plan is added to shared_preload_libraries. For details, see Configure cluster parameters.

• Both polar_sql_mapping and pg_hint_plan extensions are available in the target database.

Supported versions:

Set up ePQ Hint Injection

Enable the feature per database or per role. The following example uses the postgres database.

Step 1: Install the extensions and enable pg_hint_plan.

CREATE EXTENSION polar_sql_mapping;
CREATE EXTENSION pg_hint_plan;
ALTER DATABASE postgres SET pg_hint_plan.enable_hint TO on;

Step 2: Prepare a test table.

CREATE TABLE t1 (c1 int, c2 int);
INSERT INTO t1 SELECT generate_series(1, 1000), generate_series(1, 1000);
-- Enable parallel execution for t1.
ALTER TABLE t1 SET (px_workers = 1000);

Step 3: Register queries in the mapping tables.

-- Register queries for px mapping (prepend /*+PX()*/ when ePQ is globally off).
SELECT polar_sql_mapping.insert_px_mapping('explain select count(*) from t1;');
SELECT polar_sql_mapping.insert_px_mapping('select count(*) from t1;');

-- Register queries for nonpx mapping (prepend /*+NoPX()*/ when ePQ is globally on).
SELECT polar_sql_mapping.insert_nonpx_mapping('explain select count(*) from t1;');
SELECT polar_sql_mapping.insert_nonpx_mapping('select count(*) from t1;');

Step 4: Enable the rewrite feature.

-- Takes effect for new sessions only. Reconnect after running this statement.
ALTER DATABASE postgres SET polar_sql_mapping.use_px_sql_mapping = true;

Step 5: (Optional) Enable debug output to verify rewriting.

SET pg_hint_plan.debug_print TO on;
SET pg_hint_plan.message_level TO notice;
SET polar_sql_mapping.log_usage = notice;
SET client_min_messages TO notice;

Verify the rewrite

After reconnecting, run a registered query to confirm hint injection.

When `polar_enable_px` is off (px mapping applies):

EXPLAIN SELECT count(*) FROM t1;

Expected output:

NOTICE:  sql mapping exist. The id = 1
NOTICE:  px sql mapping: change sql to '/*+PX()*/explain select count(*) from t1;'.
NOTICE:  pg_hint_plan:
used hint:
PX()
not used hint:
duplication hint:
error hint:

                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=0.00..431.00 rows=1 width=8)
   ->  PX Coordinator 6:1  (slice1; segments: 6)  (cost=0.00..431.00 rows=1 width=8)
         ->  Partial Aggregate  (cost=0.00..431.00 rows=1 width=8)
               ->  Partial Seq Scan on t1  (cost=0.00..431.00 rows=167 width=1)
 Optimizer: PolarDB PX Optimizer
(5 rows)

When `polar_enable_px` is on (nonpx mapping applies):

SET polar_enable_px = 1;
SELECT count(*) FROM t1;

Expected output:

NOTICE:  sql mapping exist. The id = 2
NOTICE:  px sql mapping: change sql to '/*+NoPX()*/select count(*) from t1;'.
NOTICE:  pg_hint_plan:
used hint:
NoPX(0)
not used hint:
duplication hint:
error hint:

 count
-------
  1000
(1 row)

EXPLAIN SELECT count(*) FROM t1;

Expected output:

NOTICE:  sql mapping exist. The id = 1
NOTICE:  px sql mapping: change sql to '/*+NoPX()*/explain select count(*) from t1;'.
NOTICE:  pg_hint_plan:
used hint:
NoPX(0)
not used hint:
duplication hint:
error hint:

                         QUERY PLAN
------------------------------------------------------------
 Aggregate  (cost=17.50..17.51 rows=1 width=8)
   ->  Seq Scan on t1  (cost=0.00..15.00 rows=1000 width=0)
(2 rows)

Configure matching mode

By default, ePQ Hint Injection uses string matching: the query text in the mapping table must match the incoming query character-for-character, including spaces and line breaks.

To match queries regardless of whitespace differences, enable parse tree matching:

ALTER DATABASE postgres SET polar_sql_mapping.use_px_parse_match = true;

Reconnect after running this statement.

Parse tree matching increases matching time to approximately twice that of string matching. Only enable it when string matching fails due to whitespace differences.

SQL registration requirements

SQL registered with insert_px_mapping() or insert_nonpx_mapping() must meet all of the following conditions. A statement that violates any condition triggers an error.

• Must be a DQL statement — SELECT or EXPLAIN.

• Must not already contain /*+PX()*/ or /*+NoPX()*/.

• Must be syntactically valid.

SELECT instr(varchar_test, 'arh') FROM test_datatype_list WHERE varchar_test IS NOT NULL ORDER BY 1;

SELECT polar_sql_mapping.insert_px_mapping(
  'select instr(varchar_test,''arh'') from test_datatype_list where varchar_test is not null order by 1'
);

-- View px mapping entries.
SELECT * FROM polar_sql_mapping.polar_px_mapping_table;

-- View nonpx mapping entries.
SELECT * FROM polar_sql_mapping.polar_nonpx_mapping_table;

Collect slow SQL automatically

The slow SQL collection feature collects the latest N SQL statements (N is determined by the px_max_num parameter) that exceed or fall under a duration threshold and stores them in a hash table. You can then add them to the mapping table by ID, without re-typing the full SQL.

-- Collect queries that run for 3 seconds or longer.
ALTER DATABASE postgres SET polar_sql_mapping.px_record_query = 1;
ALTER DATABASE postgres SET polar_sql_mapping.px_record_upper_or_lower = 1;
ALTER DATABASE postgres SET polar_sql_mapping.px_record_duration_time = 3000;

SELECT sum(c1) FROM t1 WHERE c1 = (SELECT count(pg_sleep(3)));

SELECT * FROM polar_sql_mapping.px_query_info;

 id |                            query                             | execution_time | calls
----+--------------------------------------------------------------+----------------+-------
  1 | select sum(c1) from t1 where c1=(select count(pg_sleep(3))); |           3004 |     1
(1 row)

-- Add a single query by ID.
SELECT polar_sql_mapping.insert_px_mapping_id(1);
SELECT polar_sql_mapping.insert_nonpx_mapping_id(1);

-- Add all collected queries at once.
SELECT polar_sql_mapping.insert_px_mapping_id(id) FROM polar_sql_mapping.px_query_info;
SELECT polar_sql_mapping.insert_nonpx_mapping_id(id) FROM polar_sql_mapping.px_query_info;

NOTICE:  Parse tree match hit. Sql mapping exist. The id = 6
NOTICE:  px sql mapping: change sql to '/*+PX()*/select sum(c1) from t1 where c1=(select count(pg_sleep(3)));'.

SELECT polar_sql_mapping.px_query_info_clear();

ALTER DATABASE postgres SET polar_sql_mapping.px_record_query = 0;

Troubleshoot failed matches

If a query is not being rewritten as expected, enable debug output to inspect matching:

SET pg_hint_plan.debug_print TO on;
SET pg_hint_plan.message_level TO notice;
SET polar_sql_mapping.log_usage = notice;
SET client_min_messages TO notice;

Then re-run the query and check the NOTICE messages. Common causes of failed matches: