
- EDB
- PostgreSQL
意外な落とし穴!アプリケーション⇒DBデータ型によるパフォーマンス影響
PostgreSQLのオプティマイザがインデックスを適切に使用できない理由は様々ですが、本記事ではJDBC⇔PostgreSQL間でデータ型の不一致がインデックスの使用にどういった悪影響を及ぼすかを見ていきます
|
当記事はエンタープライズDB社のブログ記事の翻訳の紹介および弊社考察を加えています。
Vik Fearing
. (2022, Oct, 28). The Merge command.
The EDB Blog.
https://www.enterprisedb.com/blog/merge-command
MERGE文は、今回PostgreSQL 15 に実装される以前から議論されてきた機能です。Postgres wiki によると、コントリビューターがこの機能について初めて投稿したのは2005年11月のことでした。MERGE文の実装は Google Summer of Code 2010 に参加した学生にも提案されていました。Postgres 14 で実装されると期待されていましたが、今回ついに Postgres 15 で実装が行われました。
MERGE文はあるテーブルを別のテーブルにマージするために使用されます。基本的な構文は次の通りです。
MERGE INTO target USING source ON <condition>
MERGE文は、何をしたいのかによって様々な振る舞いをさせることができます。マージするデータに重複がある場合、既存の値を更新するのか、既存の値を削除するのか、 何か特別な処理をするのか、重複するデータがない時はどのような処理をするのか、これをWHEN句で指示することができます。
さっそく、次の2つのテーブルを作成してMERGE文の動作を見ていきましょう。
CREATE TABLE target (id bigint, value text); INSERT INTO target (id, value) VALUES (1, 'aspect'), (2, 'concept'), (5, 'mall'), (6, 'storage');
CREATE TABLE source (LIKE target); INSERT INTO source (id, value) VALUES (1, 'height'), (2, 'response'), (3, 'fishing'), (4, 'sister');
source 表のデータを target 表にマージしたい場合、ID 列のデータがどのようにマッチングするのかを最初に知る必要があります。それを確認するために ID 列を結合キーに指定した以下のクエリを実行します。
SELECT t.id, t.value, s.id, s.value FROM target AS t RIGHT OUTER JOIN source AS s ON s.id = t.id ORDER BY COALESCE(t.id, s.id);
クエリの結果は次のようになります。
id | value | id | value ----+---------+----+---------- 1 | aspect | 1 | height 2 | concept | 2 | response | | 3 | fishing | | 4 | sister
source 表のデータを target 表にマージするため、source 表を軸に外部結合をしています。
クエリの結果から、ID 1 と 2 は双方のテーブルに存在(MATHCED) しており、ID 3 と 4 は source 表にだけ存在(NOT MATCHED)している ということがわかります。
MERGE INTO target AS t USING source AS s ON s.id = t.id WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);
“NOT MATCHED” のデータだけを target 表にマージする場合、次のクエリで行うことができます。
INSERT INTO target (id, value) SELECT s.id, s.value FROM source AS s WHERE NOT EXISTS ( SELECT 1 FROM target AS t WHERE t.id = s.id);
source 表に存在するものを target 表からすべて削除したい場合は次のように実行します。
MERGE INTO target AS t USING source AS s ON s.id = t.id WHEN MATCHED THEN DELETE;
これは、MERGE文が実装される前だと次のように行う必要がありました。
DELETE FROM target AS t WHERE t.id IN (SELECT id FROM source);
ID 列のデータが重複(MATCH)するデータを target 表にマージしたい場合、次のように実行することができます。
MERGE INTO target AS t USING source AS s ON s.id = t.id WHEN MATCHED THEN UPDATE SET value = s.value;
MATCHED(またはNOT MATCHED)句の後に、好きな条件式(Boolean式)を追加することもできますし、これらの句はいくつでも書き足すことができます。ただし、実際に実行されるのは1番最初に条件に合致した処理だけという点には注意が必要です。
MERGEコマンドは長い間待ち望まれていたもので、ようやく実装されたことに喜びを隠せません!従来のバージョンでは、source 表に行がないことを条件に、target 表から行を削除することができず、それを実現するには2つ以上のクエリで実行する必要がありました。
PostgreSQL 15 にアップグレードして、この素晴らしい変更を体験してみてください。
(以上、EDB社ブログ記事より翻訳)
PostgreSQL 14 以前のバージョンにも UPSERT 機能はありましたが、「INSERT で制約違反が発生する場合に UPDATE に切り替えるための機能」でした。MERGE 文のように柔軟な条件判定であったり、制約違反が発生する際に DELETE を行うことはできません。MERGE文はSQLの標準規格(SQL:2003)で標準化されているため非常に汎用的です。既存の処理を無理に置き換える必要はありませんが、複数の処理をMERGE文で簡素化したり、MERGE文を使う他のDBのアプリケーションの移行にきっと役に立つはずです。
![]() |
---|
2017年に中途入社。Oracle Database、EDB Postgres/PostgreSQL のサポート経験を経て、2020年からバックサポートを担当。DBとアプリケーションを繋ぐミドルウェア製品のスペシャリスト。トレンドな技術は積極的に触れるほど好奇心旺盛。最近はプロアクティブなサポートを目指して粉骨砕身。趣味はボードゲーム。...show more
■本記事の内容について
本記事に示した定義及び条件は変更される場合があります。あらかじめご了承ください。
■商標に関して
・Oracle®、Java、MySQL及びNetSuiteは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
文中の社名、商品名等は各社の商標または登録商標である場合があります。
PostgreSQLのオプティマイザがインデックスを適切に使用できない理由は様々ですが、本記事ではJDBC⇔PostgreSQL間でデータ型の不一致がインデックスの使用にどういった悪影響を及ぼすかを見ていきます
EDB Postgres Workload Reportsは、Postgresデータベースのパフォーマンス診断とトラブルシューティングを強化する新しいツールです。OracleのAWRに似た詳細なレポートを提供し、データベースの問題を迅速に特定・解決できるようサポートします。本記事では概要と利用手順をご紹介します。
35年以上教育事業を展開しているアシストが新たに取り組み始めた「ポスグレ学園」。連載10回目となる今回の記事では、OSS-DB Gold試験対策問題集 出版の経緯や内容を 新校長 我妻にインタビューしました。