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; 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) 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$ ); 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) 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) 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.
Top comments (0)