Windows版PostgreSQLの拡張機能 pg_hint_plan をビルドする

# はじめに
PostgreSQLの拡張機能`pg_hint_plan`は、実行計画を誘導するための特殊なコメントです。
詳細：[pg_hint_planを用いて実行計画を制御する](https://jpn.nec.com/postgresql/technical_info/pg_hint_plan.html)
ところがLinux版と違い、Windows版は公式にビルドしたものをリリースしていません。

別件のトラブルで`pg_statsinfo`について調べていると、Windows版の`pg_repack`バイナリ配布しているという記事を見つけました。

* [PostgreSQL拡張のWindowsバイナリ配布はじめました(半年前から)](https://www.docswell.com/s/harukat/Z4QDPL-2024-07-04-171804)
* [第47回 PostgreSQLアンカンファレンス@オンライン](https://posfie.com/@nuko_yokohama/p/zH6v1xr?page=3)

SRA OSSとしてPostgreSQL拡張のWindowsバイナリ提供を開始しているが、今のところはサポート加入者のみで、一般配布は計画中とのこと。

SRA OSSで継続バイナリリリース

* pg_hint_plan
* [pgaudit](https://qiita.com/yaju/items/d014a0a477ccb27e99ce) 対応済
* pg_repack
* pg_dbms_stats

※2025年06月時点ではまだ一般配布は行われていません。

じゃー自力でなんとかしてみようと考えたところです。

# 環境
* Windos 11 Home 64bit
* Visual Studio 2022(C++ によるデスクトップ開発) インストール済み
* PostgreSQL 14.7

仕事の関係上、現時点では少し古めの PostgreSQL のバージョンを使用します。
PostgreSQL 14は、2021/09/30リリース

# 開発準備
`pg_hint_plan`をビルドする上で今回 Visual Studio を使用します。

プロジェクトファイルを Github に置きました。
ただし、`pg_hint_plan`のソースコードや`pg_hint_plan.dll`は含まれていませんが`queryjumble.obj`のみ含めました。

https://github.com/yaju/pg_hint_plan-REL14-msvc?tab=readme-ov-file

## pg_hint_plan を取得
下記サイトから Source code(zip) を取得します。

https://github.com/ossc-db/pg_hint_plan/releases/tag/REL14_1_4_3

筆者は、C:¥WorkSpace に展開しました。

## queryjumble.objの取得
`pg_hint_plan`をビルドには、「queryjumble.obj」ファイルが必要となります。
PostgreSQLをソースコードからビルドして取得します。

https://qiita.com/yaju/items/6b6fce0dd1898db4e962

PostgreSQLインストール配下に`obj`フォルダを作成し、「queryjumble.obj」ファイルをコピーします。
今回の筆者の場合、PostgreSQLインストールフォルダは「C:¥Program Files¥PostgreSQL¥14」になります。

## プロジェクト作成
C++でダイナミックリンクライブラリ(DLL)を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/a4b574e3-1cf7-4cf9-bd1a-6c6aea2153dc.png)
`pg_hint_plan` でプロジェクトを作成します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/e9e6abeb-ca31-4cd3-9fca-26b92bd0426a.png)

プロジェクトを一旦閉じます。
同じ拡張機能の`pg_repack`に倣って、`pg_hint_plan`フォルダから`msvc`に変更したらプロジェクトを開き直します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/ed10b758-f1e6-4ab1-8a11-353488ac11e7.png)

### ファイルの追加の既存ファイル削除
上位フォルダから「pg_hint_plan.c」ファイルを追加します。
あと、既存ファイルの下記ファイルは不要なので削除します。

* dllmain.cpp
* pch.cpp
* framework.h
* pch.h

### 定義ファイルの作成
モジュール定義ファイル(.def)を作成して、`msvc`フォルダに配置します。

```text:pg_hint_plan.def
LIBRARY pg_hint_plan
EXPORTS
    _PG_init
    _PG_fini
```    

最初は、元ソースコードに`PGDLLEXPORT`を追記する方法でビルドしていたのですが、モジュール定義を使用してエクスポート指定すると、元ソースコードの変更せずビルド出来ることが分かりました。

```c
/* Module callbacks */
PGDLLEXPORT void _PG_init(void);
PGDLLEXPORT void _PG_fini(void);
```

## プロパティ設定
`pg_hint_plan`でプロジェクトのプロパティにて、構成を「すべての構成」にします。

### プリプロセッサ定義
C/C++の「プリプロセッサ」を選択、「プリプロセッサの定義」に「WIN32」を追加します。
PostgreSQLは「WINDLL」を同等のものとして認識しないため、Win32プラットフォームの場合は明示的に指定する必要があります。
※2014年の記事を参考にしているため、もう改善されている可能性があります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/ee4aaf4d-5650-4ff7-8942-8f824c175bc0.png)

### C/C++の例外を無効化
C/C++の「コード生成」を選択、「C/C++の例外を有効にする」で「いいえ」にします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/ceb7d8ad-c1f4-4ea0-ae28-4d04916ccc27.png)

### コンパイルをC言語へ
C/C++の「詳細設定」を選択、「コンパイル言語の選択」で「C コードとしてコンパイル(/TC)」にします。
次に「指定の警告を無視する」を「4703;4996」をセットします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/79af1075-662d-4100-b51c-9943716ed16d.png)

* 4703…未使用要素
* 4996…非推奨関数

4996の非推奨関数の警告を無視したくない場合、`#define _CRT_SECURE_NO_WARNINGS`の指定や`strcpy`の代わりに`strcpy_s`へ書き換える方法もあります。

https://theolizer.com/visual_cpp/visual-c%E3%81%AEc4996%E8%AD%A6%E5%91%8A%E3%81%B8%E3%81%AE%E5%AF%BE%E5%87%A6/

### プリコンパイル済みヘッダーを使用しない
C/C++の「プリコンパイル済みヘッダー」を選択、「プリコンパイル済みヘッダー」で「プリコンパイル済みヘッダーを使用しない」にします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/05d45881-d799-49ec-bc6a-be7d6f3b647e.png)

### マニフェストを生成しない
リンカーの「マニフェスト ファイル」を選択、「マニフェストの生成」で「いいえ (/MANIFEST:NO)」にします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/b84b07eb-4efa-474c-8453-df8ca79345a5.png)

### 追加の依存関係
リンカーの「入力」を選択、「追加の依存ファイル」に「postgres.lib」と「queryjumble.obj」を追加します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/0c694fef-2567-4f4c-a230-ffb7063f8150.png)
「親またはプロジェクトの既定値から継承」のチェックは外さないでください。
単純な拡張機能では必要ありませんが、サーバー関数にリンクする場合は必要になります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/0da12a9d-d067-46bd-8fd0-77c5e40232a0.png)

### モジュール定義関係
モジュール定義ファイル「pg_hint_plan.def」をセットします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/8e594d84-1adc-47db-9bd8-2565f60250f3.png)

### インクルードディレクトリ
C/C++の「全般」を選択、「追加のインクルードディレクトリ」で、PostgreSQLインストール配下の4つのフォルダを追加します。
* include\server\port\win32_msvc
* include\server\port\win32
* include\server
* include

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/96657a51-42ad-4b61-b173-f0a86b97a35b.png)

今回の筆者の場合、PostgreSQLインストールフォルダは「C:\Program Files\PostgreSQL\14」になります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/8fd8a6a5-2c5d-483f-9ff1-5a5f80f2cb60.png)

### ライブラリディレクトリ
リンカーの「全般」を選択、「追加のライブラリ ディレクトリ」で、PostgreSQLインストール配下の`lib`フォルダと`obj`フォルダを追加します。
次に「ライブラリ依存関係のリンク」を「いいえ」にします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/6a3eb80a-dc5c-4762-9039-c66ecf616330.png)

`obj`フォルダは前述した「queryjumble.obj」用です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/46393943-bd0b-4814-a889-83da718e74ce.png)

### プロジェクトの構築
プルダウンメニューから「Relese」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/4e18549e-f48e-4ca1-b070-9e71797903f8.png)

# プログラム
## 構成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/7be550a2-6ea3-4805-8262-147040e554ea.png)

`msvc`フォルダの中身、x64¥Releseフォルダに「pg_hint_plan.dll」ファイルが生成される。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14053/1ffcba3f-bdb4-4c4c-8fb5-8ed5b7b9e1f7.png)

## 修正内容
>関数_PG_initと _PG_fini の宣言と定義の両方にPGDLLEXPORTマクロを追加するだけで済みました。
ただし、関数 _PG_init はpostgresが提供するインクルードファイル server\plpgsql.h で宣言されているため、コンパイラエラーpg_hint_plan.c(667,19): error C2375: '_PG_init': redefinition; different linkageを回避するために、関数の宣言にも同様の修正が必要です
https://github.com/ossc-db/pg_hint_plan/issues/201

最初はissues 201に従って元ソースコードを修正する方法だったため、修正方法を記載していたのですがモジュール定義ファイルの方法にしたため、元ソースコードを修正する必要がなくなりました。


## ビルド時の不明点
警告として下記メッセージが出たのですが、Grep検索しても関数が見つからないです。
もし何か知っている方がいたら、コメントをお願いします。

```
'pg_hint_plan_standard_join_search' の関数定義が見つかりません。
'pg_hint_plan_make_join_rel' の関数定義が見つかりません。
'pg_hint_plan_join_search_one_level' の関数定義が見つかりません。
```

# 導入
## 配置
今回の筆者の場合、PostgreSQLインストールフォルダは「C:¥Program Files¥PostgreSQL¥14」になりますので、下記に配置します。

|ファイル|配置先|
|:--|:--|
|pg_hint_plan.dll|lib|
|pg_hint_plan.control|share\extension|
|pg_hint_plan--1.3.0.sql|同上|
|pg_hint_plan--1.3.0--1.3.1.sql|同上|
|pg_hint_plan--1.3.1--1.3.2.sql|同上|
|pg_hint_plan--1.3.2--1.3.3.sql|同上|
|pg_hint_plan--1.3.3--1.3.4.sql|同上|
|pg_hint_plan--1.3.4--1.3.5.sql|同上|
|pg_hint_plan--1.3.5--1.3.6.sql|同上|
|pg_hint_plan--1.3.6--1.3.7.sql|同上|
|pg_hint_plan--1.3.7--1.3.8.sql|同上|
|pg_hint_plan--1.3.8--1.3.9.sql|同上|
|pg_hint_plan--1.3.9--1.3.10.sql|同上|
|pg_hint_plan--1.3.10--1.4.sql|同上|
|pg_hint_plan--1.4.1--1.4.2.sql|同上|
|pg_hint_plan--1.4--1.4.1.sql|同上|
|pg_hint_plan--1.4.2--1.4.3.sql|同上|

こんなにsqlファイルが必要なのか、1つでいいのではないかと思ったのですが、`pg_hint_plan.control` に書かれた`default_version`(例: 1.4.3）のように、バージョン3桁のみのファイル(例 1.4.3.sql)が無い場合、バージョン3桁のみファイルから指定バージョンまでアップデートしていく仕組みになっているようです。

## postgres.confの修正
設定ファイルに`pg_hint_plan`の事前ロード用の設定を追加します。

```ini:postgres.conf
shared_preload_libraries = 'pg_hint_plan'
```

ファイル編集後、PostgreSQLサーバを起動、または再起動してください。
※shared_preload_librariesには、カンマ区切りで複数のモジュールが登録できます。

## PostgreSQLに拡張機能の登録
```sql
CREATE EXTENSION pg_hint_plan;
```

登録の確認

```sql
SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_hint_plan';
```

|extname|extversion|
|:--|:--|
|pg_hint_plan|1.4.3|

## 検証
### 検証データ作成
ChatGPTで検証するSQLを作成してもらいました。

```sql:基本
CREATE TABLE test_table (
    id serial PRIMARY KEY,
    name text,
    created_at timestamp
);

INSERT INTO test_table (name, created_at)
SELECT
    'name_' || s,
    now() - (s || ' days')::interval
FROM generate_series(1, 10000) AS s;
```

```sql:応用
CREATE TABLE t1 (id int, val text);
CREATE TABLE t2 (id int, val text);

INSERT INTO t1 SELECT i, 'A' FROM generate_series(1, 1000) i;
INSERT INTO t2 SELECT i, 'B' FROM generate_series(1, 100000) i;
```

### 実行プラン基本の確認
ヒント句なしの通常プランの場合、インデックスが効いています。

```sql
EXPLAIN SELECT * FROM test_table WHERE id = 1234;

-- 結果
QUERY PLAN
Index Scan using test_table_pkey on test_table  (cost=0.29..8.30 rows=1 width=21)
  Index Cond: (id = 1234)
```

ヒント句で`SeqScan`を指定した場合、インデックスが効かなくなっています。

```sql
EXPLAIN SELECT /*+ SeqScan(test_table) */ * FROM test_table WHERE id = 1234;

-- 結果
QUERY PLAN
Seq Scan on test_table  (cost=0.00..189.00 rows=1 width=21)
  Filter: (id = 1234)
```

### 実行プラン応用の確認

ヒント句なしの通常プランの場合、`Hash Join`や`Merge Join`になることが多いです。

```sql
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;

-- 結果
QUERY PLAN
Hash Join  (cost=27.50..1855.50 rows=1000 width=12)
  Hash Cond: (t2.id = t1.id)
  ->  Seq Scan on t2  (cost=0.00..1443.00 rows=100000 width=6)
  ->  Hash  (cost=15.00..15.00 rows=1000 width=6)
        ->  Seq Scan on t1  (cost=0.00..15.00 rows=1000 width=6)
```

ヒント句で`NestLoop(t1 t2)`を強制した場合

```sql
EXPLAIN SELECT /*+ NestLoop(t1 t2) Leading(t1 t2) */ * 
FROM t1 JOIN t2 ON t1.id = t2.id;

-- 結果
QUERY PLAN
Nested Loop  (cost=0.00..1501460.50 rows=1000 width=12)
  Join Filter: (t1.id = t2.id)
  ->  Seq Scan on t2  (cost=0.00..1443.00 rows=100000 width=6)
  ->  Materialize  (cost=0.00..20.00 rows=1000 width=6)
        ->  Seq Scan on t1  (cost=0.00..15.00 rows=1000 width=6)
```

# 最後に
なんとか、`pg_hint_plan`をビルドすることが出来ました。このissue 201がなかったら諦めてたと思います。

https://github.com/ossc-db/pg_hint_plan/issues/201

今回はPostgreSQL 14でしたが、バージョンごとに導入物を用意する必要があるようです。
SRA OSSで一般配布をして頂けるのがいいですけどね。

Windows版PostgreSQLの拡張機能もサポートがもっと充実するようになればいいのにな。