2.11.2 hint_plan.hintsテーブルのtext型norm_query_string列を、bigint型のquery

非互換

Fujitsu Enterprise Postgres 17では、hint_plan.hintsテーブルのtext型norm_query_string列をbigint型のquery_id列に変更しました。

hint_plan.hintsテーブルにSQL文とヒント句を登録しておく際の、登録方法や管理方法が異なるため、運用の手順が異なります。

また、hint_plan.hintsテーブルに登録したSQL文の構文に対する評価方法も異なります。

登録方法の違い

Fujitsu Enterprise Postgres 16 SP1以前の場合

SQL文の文字列をnorm_query_string列に直接指定して挿入します。

INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
     VALUES ('SELECT * FROM s1.t1 WHERE t1.c1 = ?;', '', 'SeqScan(t1)');

Fujitsu Enterprise Postgres 17の場合

SQL文の文字列からデータベースで生成される一意のクエリIDを取得し、query_id列に挿入します。
一意のクエリIDは、GUCパラメタのcompute_query_idを有効にし、EXPLAINコマンドのVERBOSEオプションを指定しSQL文を実行することで取得できます。

=# SET compute_query_id TO ON;
SET

=# EXPLAIN (VERBOSE, COSTS false)SELECT * FROM s1.t1 WHERE t1.c1 = 1;
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on s1.t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8, c9, c10
   Filter: (t1.c1 = 1)
 Query Identifier: 962480014996954778

=# INSERT INTO hint_plan.hints (query_id, application_name, hints)
	VALUES (962480014996954778, '', 'SeqScan(t1)');
INSERT 0 1

登録したSQL文の管理方法の違い

Fujitsu Enterprise Postgres 16 SP1以前の場合

SQL文の文字列をhint_plan.hintsテーブルに保存するため、hint_plan.hintsテーブルを参照するこで確認できます。

=# SELECT * FROM hint_plan.hints;
 id |      norm_query_string               | application_name |    hints
----+--------------------------------------+------------------+-------------
  1 | SELECT * FROM s1.t1 WHERE t1.c1 = ?; |                  | SeqScan(t1)

Fujitsu Enterprise Postgres 17の場合

hint_plan.hintsテーブルには登録時に指定したクエリIDしか保存されていません。
そのため、hint_plan.hintsテーブルに格納したヒント句が有効なSQL文の文字列は、利用者で保存して管理しておく必要があります。
なお、pg_stat_statementsを利用可能としている場合は、以下のように、hint_plan.hintsと表結合することで、保存されたクエリIDからSQL文を確認することが可能になります。

=# SELECT h.id, s.query, s.queryid, h.hints FROM pg_stat_statements s 
       RIGHT JOIN hint_plan.hints h ON s.queryid = h.query_id;
 id |              query                   |       queryid        |    hints
----+--------------------------------------+----------------------+-------------
  1 | SELECT * FROM s1.t1 WHERE t1.c1 = $1 | 962480014996954778   | SeqScan(t1)

注意

pg_stat_statementsに保存できるSQL文の数はpg_stat_statements.maxパラメタの指定値に依存するなど、永続的な情報ではありません。登録直後の確認で利用するなどの範囲で利用してください。

登録したSQL文の評価方法の違い

Fujitsu Enterprise Postgres 16 SP1以前の場合

hint_plan.hintsテーブルに保存したSQL文の文字列と、実行したSQL文が空白や大文字小文字などを含めて一致している場合のみ、登録したヒント句が使用されます。
「登録方法の違い」で登録したSQL文に対して、以下のSQL文では登録したヒント句は実行されません。

select * FROM s1.t1 WHERE t1.c1 = 1;
SELECT * FROM s1.t1 WHERE T1.C1 = 1;
SELECT * FROM s1.t1 WHERE c1 = 1;

Fujitsu Enterprise Postgres 17の場合

SQL文から生成されたクエリIDが同じ場合は、登録したヒント句が使用されます。
クエリIDはSQL文の意味解析されてた上で生成されているため、SQL文の文字列が完全一致でない場合でも、同じ構文だと判断されると、登録したヒント句が使用されます。
「登録方法の違い」で登録したSQL文に対して、以下のSQL文であっても登録したヒント句が実行されます。

select * FROM s1.t1 WHERE t1.c1 = 1;
SELECT * FROM S1.T1 WHERE   T1.C1 = 3;
SELECT * from s1.t1 WHERE c1 = 4;

対処方法

Fujitsu Enterprise Postgres 16 SP1以前のhint_plan.hintsテーブルを利用している場合は、以下の手順で移行してください。

なお、pg_hint_planのアップデートを行うとhint_plan.hintsテーブルのデータが削除されてしまうため、アップデートの前に必ず移行してください。

COPYコマンドでhint_plan.hintsテーブルから一般ファイルに退避してください。
利用者でSQL文を管理していない場合は、このファイルを保存しておいてください。
移行用の一時テーブルtmp_hint_tableを作成します。
```
CREATE TABLE tmp_hint_table
AS
SELECT * FROM hint_plan.hints;
```

SQL文からのクエリIDを取得する、以下のget_query_id関数を作成します。

CREATE FUNCTION get_query_id(text) RETURNS bigint
LANGUAGE plpgsql AS
$$
DECLARE
  query text;
  explain_output text;
  query_id bigint;
BEGIN
  set compute_query_id TO ON;
  query = 'EXPLAIN (VERBOSE, FORMAT json) ' || $1;
  EXECUTE query INTO explain_output;
  SELECT INTO query_id ((explain_output::jsonb)->0->'Query Identifier')::bigint;
  return query_id;
END;
$$;

pg_hint_planのアップデートを行います。
以下を実行して、作業用テーブルから新しいhint_plan.hintsテーブルにデータを移行します。
```
INSERT INTO hint_plan.hints (query_id,application_name, hints) SELECT get_query_id( norm_query_string), application_name, hints from tmp_hint_table order by id;
```
注意
クエリIDを取得するうえで、EXPLAINコマンドをVERBOSEオプションで実行しているため、SQL文によってはそのままでは移行できない場合があります。エラーとなった場合は、移行用の一時テーブルtmp_hint_tableから、該当するレコードの更新や削除にて対処してください。
- EXPLAINコマンドを含むSQL文は移行できません。該当するレコードは削除してください。
- 代入の?を含む場合は、実際のデータ(数値であれば1、文字列であれば'a'など)に置き換えが必要です。
  [例]
  置き換え前）
```
SELECT * FROM test1 WHERE col1 = ? and col2 = ?;
```
  置き換え後）
```
SELECT * FROM test1 WHERE col1 = 1 and col2 = 'a';
```
- SQL文が異なっていても同じクエリIDになり、一意性制約違反で挿入できない場合があります。重複した場合は該当するレコードを削除してください。
- SQL文は実行計画を元に評価されるため、SQL文での操作対象のリソースは存在していなければなりません。

作業用のテーブルと関数を削除します。

DROP TABLE tmp_hint_table;
DROP FUNCTION get_query_id;

2.11.2 hint_plan.hintsテーブルのtext型norm_query_string列を、bigint型のquery_id列に変更