DEV Community

Franck Pachot for YugabyteDB

Posted on

3 1

pg_stat_statements with pg_hint_plan

I got this question when presenting pg_hint_plan at the PgDay Austria (I'll present it again, online, at postgresconf.org): does the queryid in pg_stat_statements change when hints are added to the query?

The short answer is: no, it doesn't change, the queryid is calculated from the post-parse-analysis tree, ignoring the comments, and this includes hints.

But this also answers another question: do we see the injected hints in the query text? Here is a little demo to show that, even when the query is modified from the hint_plan.hints table, the queryid and query text is the same are the same as without hints.

I create the tables from my demo:

create table a (n int primary key, x int);
create table b (n int primary key, x int);
create table c (n int primary key, x int);
create index b_index on b(x) include (n);
select pg_stat_statements_reset();
select * from c "🍒" natural join b "🍌" natural join a "🍏" ;
select queryid, calls, rows, query from pg_stat_statements;
Enter fullscreen mode Exit fullscreen mode

I check the queryid from pg_stat_statements:

yugabyte=# select * from c "🍒" natural join b "🍌" natural join a "🍏" ;
select queryid, calls, rows, query from pg_stat_statements;LOG:  statement: select * from c "🍒" natural join b "🍌" natural join a "🍏" ;
 n | x
---+---
(0 rows)

yugabyte=# select queryid, calls, rows, query from pg_stat_statements;

       queryid       | calls | rows |                           query
---------------------+-------+------+-----------------------------------------------------------
 6990186059047281266 |     1 |    1 | select pg_stat_statements_reset()
 6386600050796028530 |     1 |    0 | select * from c "🍒" natural join b "🍌" natural join a "🍏"
(2 rows)
Enter fullscreen mode Exit fullscreen mode

Now with the hints from the hint_plan table:

grant yb_extension to yugabyte;
create extension pg_hint_plan;
set pg_hint_plan.enable_hint_table=on;
insert into hint_plan.hints(norm_query_string, application_name, hints) values (
$$select * from c "🍒" natural join b "🍌" natural join a "🍏" ;$$,
$$$$,
$hints$Leading( ( ("🍏" "🍌") "🍒" ) ) HashJoin( "🍏" "🍌" ) HashJoin("🍏" "🍌" "🍒") SeqScan("🍏") IndexOnlyScan("🍌") SeqScan("🍒")$hints$
);
Enter fullscreen mode Exit fullscreen mode

I see the same statement and and queryid:

yugabyte=# select * from c "🍒" natural join b "🍌" natural join a "🍏" ;
select queryid, calls, rows, query from pg_stat_statements; n | x
---+---
(0 rows)

yugabyte=# select queryid, calls, rows, query from pg_stat_statements;
       queryid        | calls | rows |                                                                              query
----------------------+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 -4857947532354760446 |     3 |    1 | SELECT hints   FROM hint_plan.hints  WHERE norm_query_string = $1    AND ( application_name = $2     OR application_name = '' )  ORDER BY application_name DESC
  6990186059047281266 |     1 |    1 | select pg_stat_statements_reset()
  6386600050796028530 |     1 |    0 | select * from c "🍒" natural join b "🍌" natural join a "🍏"
(3 rows)
Enter fullscreen mode Exit fullscreen mode

Then, how can I know that the hint was used? I can enable the verbose logs:

yugabyte=# set pg_hint_plan.debug_print=verbose;
SET
yugabyte=# set client_min_messages = log;
SET
yugabyte=# select pg_stat_statements_reset();
LOG:  statement: select pg_stat_statements_reset();
LOG:  pg_hint_plan[qno=0x1a]: no match found in table:  application name = "psql", normalized_query="select pg_stat_statements_reset();"
LOG:  hints in comment="(none)", query="select pg_stat_statements_reset();", debug_query_string="select pg_stat_statements_reset();"
LOG:  pg_hint_plan[qno=0x1c]: no match found in table:  application name = "psql", normalized_query="select pg_stat_statements_reset();"
LOG:  hints in comment="(none)", query="select pg_stat_statements_reset();", debug_query_string="select pg_stat_statements_reset();"
LOG:  pg_hint_plan[qno=0x1a]: planner: no valid hint
 pg_stat_statements_reset
--------------------------

(1 row)

yugabyte=# select * from c "🍒" natural join b "🍌" natural join a "🍏" ;
select queryid, calls, rows, query from pg_stat_statements;LOG:  statement: select * from c "🍒" natural join b "🍌" natural join a "🍏" ;
LOG:  pg_hint_plan[qno=0x1e]: post_parse_analyze_hook: hints from table: "Leading( ( ("🍏" "🍌") "🍒" ) ) HashJoin( "🍏" "🍌" ) HashJoin("🍏" "🍌" "🍒") SeqScan("🍏") IndexOnlyScan("🍌") SeqScan("🍒")": normalized_query="select * from c "🍒" natural join b "🍌" natural join a "🍏" ;", application name ="psql"
LOG:  pg_hint_plan[qno=0x1c]: planner
LOG:  pg_hint_plan[qno=0x1c]: setup_hint_enforcement index deletion: relation=16659(c), inhparent=0, current_hint_state=0xe094648, hint_inhibit_level=0, scanmask=0x1
LOG:  pg_hint_plan[qno=0x1c]: setup_hint_enforcement index deletion: relation=16654(b), inhparent=0, current_hint_state=0xe094648, hint_inhibit_level=0, scanmask=0x12
LOG:  pg_hint_plan[qno=0x1c]: setup_hint_enforcement index deletion: relation=16649(a), inhparent=0, current_hint_state=0xe094648, hint_inhibit_level=0, scanmask=0x1
LOG:  pg_hint_plan[qno=0x1c]: HintStateDump: {used hints:IndexOnlyScan(🍌)SeqScan(🍏)SeqScan(🍒)HashJoin(🍌 🍏)HashJoin(🍌 🍏 🍒)Leading(((🍏 🍌) 🍒))}, {not used hints:(none)}, {duplicate hints:(none)}, {error hints:(none)}
 n | x
---+---
(0 rows)

Enter fullscreen mode Exit fullscreen mode

When pg_hint_plan.debug_print=verbose the hints that have been injected by pg_hint_plan.enable_hint_table=on are visible in the HintStateDump.

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

AWS GenAI LIVE!

GenAI LIVE! is a dynamic live-streamed show exploring how AWS and our partners are helping organizations unlock real value with generative AI.

Tune in to the full event

DEV is partnering to bring live events to the community. Join us or dismiss this billboard if you're not interested. ❤️