PolarDB:ePQ Hint Injection

Prerequisites

Overview

The ePQ Hint Injection feature is used with Multi-Node Elastic Parallel Query (ePQ). During database O&M, when you cannot directly add parallel execution (PX) hints to the SQL, you can use the ePQ Hint Injection feature to add PX hints. You can configure whether to allow an SQL run ePQ Hint Injection or not without the need to modify the SQL.

Usage

Create a plug-in and enable the feature

create extension polar_sql_mapping;
create extension pg_hint_plan;
alter database postgres set pg_hint_plan.enable_hint TO on;

Use the ePQ Hint Injection feature

The following sample SQL statements describe the ePQ Hint Injection feature:

explain select count(*) from t1;
select count(*) from t1;

Prepare data

create table t1(c1 int,c2 int);
insert into t1 select generate_series(1,1000), generate_series(1,1000);
-- Configure the px_workers parameter to enable parallel execution for the t1 table.
alter table t1 set(px_workers=1000);

Write SQL statements to the mapping table

-- Create a plug-in
create extension polar_sql_mapping;
create extension pg_hint_plan;
alter database postgres set pg_hint_plan.enable_hint TO on;
-- Write SQL statements to the px mapping table
select polar_sql_mapping.insert_px_mapping('explain select count(*) from t1;');
select polar_sql_mapping.insert_px_mapping('select count(*) from t1;');
-- Write SQL statements to the nonpx mapping table
select polar_sql_mapping.insert_nonpx_mapping('explain select count(*) from t1;');
select polar_sql_mapping.insert_nonpx_mapping('select count(*) from t1;');

Enable the rewrite feature

We recommend that you set the parameters based on your database. In this example, the name of the current database is postgres.

-- Enable the rewrite feature
-- Note that the alter database postgres statement takes effect only for new sessions. You need to restart the connection for the settings to take effect. postgres can be replaced with the name of your current database.
alter database postgres set polar_sql_mapping.use_px_sql_mapping=true;

-- Enable Notice to view messages 
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;

-- Check the rewrite results.
explain select count(*) from t1;

The following output indicates that the rewrite is successful:

postgres=# explain select count(*) from t1;
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)

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

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

If polar_enable_px is set to on, the query will be rewritten to one that does not use the PX feature:

set polar_enable_px=1;
explain select count(*) from t1;

Sample result:

postgres=# set polar_enable_px=1;
SET
postgres=# select count(*) from t1;
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)

postgres=# explain select count(*) from t1;
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)

Enable parse_tree matching

By default, ePQ Hint Injection uses string matching. Spaces and line breaks need to match exactly. ePQ Hint Injection also offers a parse_tree matching mode, which ignores spaces and line breaks. However, parse_tree mode increases matching time to twice that of string matching. You can enable this mode by setting the polar_sql_mapping.use_px_parse_match parameter to true:

-- A matching failed due to spaces.
explain select count(*) from t1;
-- Enable parse_tree matching
alter database postgres set polar_sql_mapping.use_px_parse_match =true;
-- Restart the session
-- Check whether the matching is successful
explain select count(*) from t1;

Sample result:

postgres=# explain select count(*) from t1;
                         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)

postgres=# alter database postgres set polar_sql_mapping.use_px_parse_match=true;
ALTER DATABASE

postgres=# explain select count(*) from t1;
NOTICE:  Parse tree match hit. 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)

Failed matches

If the match fails, you can modify the following parameters to check the logs.

-- Enable Notice to view the messages 
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;

SQL of the extend protocol and PreparedStmt

Most applications use PreparedStmt. When you write a new SQL statement, use placeholders $n instead of ?.

An SQL statement that contains quotation marks (')

If the SQL statement contains quotation marks ('), you must add another pair of quotation marks (') for the matching.

Example:

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

The statement needs to be modified as follows.

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 the SQL statements that participate in mapping

-- View the contents of the mapping table for which the ePQ feature is used for queries
postgres=# select * from polar_sql_mapping.polar_px_mapping_table;
 id |                   source_sql                   |                       target_sql

----+------------------------------------------------+---------------------------------
------------------------
  1 | select * from polar_sql_mapping.px_query_info; | /*+PX()*/select * from polar_sql
_mapping.px_query_info;
  2 | select count(*) from t1;                       | /*+PX()*/select count(*) from t1
;
(2 rows)

-- View the content of the mapping table which the ePQ feature is used for queries
postgres=# select * from polar_sql_mapping.polar_nonpx_mapping_table;
 id | source_sql | target_sql
----+------------+------------
(0 rows)

Slow SQL collection feature

The slow SQL collection feature collects the latest N SQL(N is determined by the px_max_num parameter) with a duration no less than the threshold or no larger than the threshold into the hash table. By executing select polar_sql_mapping.insert_px_mapping_id(id);, you can add the collected SQL to the mapping table without the need to enter the entire SQL statement. Parameters:

Query collection tables

px_psm_query_info_internal() is a built-in function of the plug-in, which you can use to query the complete hash table.

select * from polar_sql_mapping.px_query_info;

Clear a collection table

You can use the following statement to clear a collection table:

select polar_sql_mapping.px_query_info_clear();

Example

-- Set parameters based on the database.
alter database postgres set polar_sql_mapping.px_record_query=1;
alter database postgres set polar_sql_mapping.px_record_upper_or_lower=1;
-- SQL statements with a duration no less than 3 seconds are collected.
alter database postgres set polar_sql_mapping.px_record_duration_time=3000;

-- Execute the SQL statements with a duration no less than 3 seconds.
select sum(c1) from t1 where c1=(select count(pg_sleep(3)));

Query the polar_sql_mapping.px_query_info table. The following result indicates that the query is successful.

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 an SQL statement to the mapping table

-- Add an SQL statement to polar_px_mapping_table
select polar_sql_mapping.insert_px_mapping_id(1);
-- Add an SQL statement to polar_nonpx_mapping_table
select polar_sql_mapping.insert_nonpx_mapping_id(1);

Add all SQL statements to the mapping table

-- Add all queries to polar_px_mapping_table
select polar_sql_mapping.insert_px_mapping_id(id) from polar_sql_mapping.px_query_info;
-- Add all queries to polar_nonpx_mapping_table.
select polar_sql_mapping.insert_nonpx_mapping_id(id) from polar_sql_mapping.px_query_info;

Rewrite

Sample result:

postgres=# select sum(c1) from t1 where c1=(select count(pg_sleep(3)));
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)));'.
NOTICE:  pg_hint_plan:
used hint:
PX()
not used hint:
duplication hint:
error hint:

 sum
-----
   1
(1 row)

Disable the collection feature

To improve performance, disable the collection function when it is not in use.

alter database postgres set polar_sql_mapping.px_record_query=0;