差分

このページの2つのバージョン間の差分を表示します。

--- it技術:データベース:postgresql [2021/11/22 13:24] – [PostgreSQL12情報] yajuadmin
+++ it技術:データベース:postgresql [2024/04/24 16:37] (現在) – [複合インデックス] yajuadmin
@@ 行 55: / 行 55: @@
 |12.0|2019/10/03|[[it技術:データベース:postgresql#postgresql12情報|postgresql12情報]] |
 |13.0|2020/09/24|[[it技術:データベース:postgresql#postgresql13情報|postgresql13情報]] |
+|14.0|2021/09/30|[[it技術:データベース:postgresql#postgresql14情報|postgresql14情報]] |
+|15.0|2022/10/06|[[it技術:データベース:postgresql#postgresql15情報|postgresql15情報]] |
+|16.0|2023/09/14|[[it技術:データベース:postgresql#postgresql16情報|postgresql16情報]] |
 ==== サポート終了期限(EOL＝End Of Life) ====
 https://www.postgresql.org/support/versioning/
 ^バージョン^初期リリース日^サポート終了期限^
-|14|2020年09月30日|2026年11月12日|
+|16|2023年09月14日|2028年11月9日|
+|15|2022年10月13日|2027年11月11日|
+|14|2021年09月30日|2026年11月12日|
 |13|2020年09月24日|2025年11月13日|
 |12|2019年10月3日|2024年11月14日|
@@ 行 135: / 行 139: @@
   * テーブルアクセスメソッドに対応
   * REINDEX CONCURRENTLYが追加、重いロックなしでインデックスを再構築可能
-  * WITH句で指定したクエリ結果を一時テーブルみたいな形で扱うことができるようになった(サブクエリー的な感じ)
+  * WITH句の共通テーブル式(CTE)で同じCTEが2回以上ある場合に[[https://www.sraoss.co.jp/tech-blog/pgsql/pg12-cte/|高速化]]
 === 注意 ===
@@ 行 191: / 行 195: @@
   * ANALYZEコマンドのパフォーマンス改善により大幅に高速化
+==== PostgreSQL15情報 ====
+-10-06にリリース
+=== 主な新機能 ===
+[[https://www.sraoss.co.jp/tech-blog/pgsql/pg15report/|PostgreSQL 15検証報告]]\\
+[[https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2022/09/pg15_report_20220906_rev2.pdf|PostgreSQL15 検証レポート - pdf]]\\
+[[https://speakerdeck.com/takahashiikki/postgresql15-new-information|アプリケーションエンジニアから見たPostgreSQL15 の新機能]]
+  * Merge文のサポート
+  * 正規表現関数の追加
+  * NOT IN句の改善
+  * ソートの性能改善
+  * ウィンドウ関数の性能改善
+  * psql \copyの性能改善
+  * zstandard圧縮サポート
+  * 先読みによるリカバリ性能改善
+  * SQL/JSON対応の拡張
+  * 多重範囲型への集約
+  * ロジカルレプリケーションの機能拡張
+  * 呼び出し元権限で実行されるビュー
+  * モジュールによるWALアーカイブ
+  * JSONLOG形式
+  * モニタリングビューの追加
+  * ロジカルレプリケーションの機能拡張
+  * パラレルクエリの強化
+  * バージョン非互換対応(新機能ではないけど)
+    * PublicスキーマのCreate権限がデフォルトからなくなる
+==== PostgreSQL16情報 ====
+-09-14にリリース
+=== 主な新機能 ===
+[[https://www.sraoss.co.jp/tech-blog/pgsql/pg16report/|PostgreSQL 16検証報告]]\\
+[[https://community.hpe.com/t5/hpe-blog-japan/%E7%AF%A0%E7%94%B0%E3%81%AE%E8%99%8E%E3%81%AE%E5%B7%BB-postgresql-16-beta-1-%E6%96%B0%E6%A9%9F%E8%83%BD%E6%A4%9C%E8%A8%BC%E7%B5%90%E6%9E%9C-%E5%85%AC%E9%96%8B/ba-p/7188972?profile.language=ja|篠田の虎の巻「PostgreSQL 16 Beta 1 新機能検証結果」公開！]]
+  * 性能向上
+    * パラレルクエリ対応追加
+    * ウィンドウ関数の最適化
+    * 同時データ投入の性能向上
+    * SIMD CPUアクセラレータ対応
+  * SQL機能
+    * SQL/JSON 対応
+    * 整数リテラル表現の追加
+    * ICU照合順序の拡張
+    * ロジカルレプリケーション機能追加
+    * スタンバイ上でのパブリケーション
+    * トランザクションの並列適用
+    * 双方向ロジカルレプリケーション
+  * クライアント機能
+    * libpqロードバランス
+    * psqlで拡張プロトコル対応
+    * pg_dump圧縮オプション追加
+  * 運用管理
+    * 新たなモニタリング項目
+    * 新たな定義済みロール
+    * ページ凍結の改善
+    * VACUUMリングバッファ指定
+    * Mesonビルド
 ===== 設定について =====
 postgresql.confの設定値は、下記SQLで context の値により設定反映のタイミングが違う。
@@ 行 323: / 行 385: @@
 search_path = 'test,"$user", public'	# schema names
 </code>
+==== psqlの使い方 ====
+psqlの機能やの使い方を紹介しているスライド資料\\
+[[https://event.ospn.jp/slides/OSC2022_OnlineKyoto/OSC-kyoto-psql.pdf|psqlの使い方 - pdf]]
 ==== psqlのパスワード入力省略 ====
 パスワード入力なしでpsqlを実行するには、下記フォルダの「pgpass.conf」にパスワードを設定しておく。
@@ 行 560: / 行 626: @@
        CONNECTION LIMIT = -1;
 </code>
+==== 複合インデックス ====
+複合インデックスは、キーの先頭から途中までのカラムが指定されていればインデックスが使われる。
+PostgreSQLの場合、インデックスが使われる為には、先頭のキーが必ず必要になる。\\
+[[https://sgyatto.hatenablog.com/entry/2018/11/24/225938|複合インデックスは、構成列のどの列を指定すれば選択候補に挙がるのか。]]
+あるテーブルのカラム(C1, C2, C3) に対して複合インデックスを貼っている状況を考えます。\\
+C1, C2, C3 の順で順序付けされています。
+^C1^C2^C3^複合インデックス利用可不可^
+|○|○|○|可|
+|○|○|×|可|
+|○|×|×|可|
+|○|×|○|可 ※C1のみ、C3は無視|
+|×|○|○|可 ※table scanが選択される可能性高い|
+|×|○|×|可 ※table scanが選択される可能性高い|
+|×|×|○|可 ※table scanが選択される可能性高い|
+|×|×|×|不可|
+=== bloomインデックス ===
+bloomインデックスは、その名前のとおり、ブルームフィルタを用いて、条件を満たさないタプルを高速に除外する（＝条件を満たす「かもしれない」タプルを残す）ことが可能なインデックスメソッドです。
+主キーが複数の場合のインデックスにはデフォルトのb-treeインデックスが使用されるが、弱点として先頭のキーが外れるとインデックスが使われなくなる。その場合に複数列インデックスには、bloomインデックスを使用することで、先頭のキーが条件が外れていてもインデックスが使われる。\\
+[[https://qiita.com/nuko_yokohama/items/4a7fe2a731a479189034|bloomインデックス]]
+bloomインデックスの本領は、複数列に対するインデックス設定をするときに発揮されます。bloom複合列インデックスの場合は、定義した列の順序に関係なくインデックス検索→Birmap Scanになります。B-Treeインデックスが使われるたケースと比べると検索時間は遅くなりますが、SeqScanほど遅くはないという検索時間になります。
+<code sql>
+-- 拡張追加
+create extension bloom
+-- 拡張確認
+SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL
+-- bloomインデックスを追加
+CREATE INDEX idx_res_match ON res_match USING bloom (factory_cd, line_cd, seq_no, lotserial, cp_cd)
+</code>
+==== 実行計画の取得 ====
+SQLの前に「EXPLAIN」か「EXPLAIN ANALYZE」を付ける。\\
+EXPLAIN は、実行計画を取得できます。\\
+「ANALYZE」オプションを使用することで、予想コストに加え、実際にかかった処理時間等の情報も確認できます。\\
+(※ ANALYZEオプションを付与すると、実際にクエリが実行されてしまうため、INSERT文や、DELETE文などを行う際は注意が必要です。)
+  * [[https://www.casleyconsulting.co.jp/blog/engineer/259/|PostgreSQLの実行計画について調べてみた]]
+  * [[https://www.slideshare.net/MikiShimogai/postgre-sql-explain|PostgreSQLクエリ実行の基礎知識 ～Explainを読み解こう～ - slideshare]]
+^主な演算子一覧^^^
+^分類^演算子^処理^
+|テーブルスキャン|Seq scan|インデックスを使用せず、全件を検索|
+|  |Index scan|インデックスを使用してスキャン|
+|  |Bitmap scan|ビットマップを使用してスキャン|
+|  |Index only scan|問い合わせがインデックスに含まれるカラムのみで完結する場合のスキャン|
+|  |Tid scan|検索条件がタプルID(ctid)のスキャン|
+|その他のスキャン|Function scan|関数がデータをgatherした結果をスキャン|
+|テーブルの結合|Nested Loop|ネステッド・ループ結合を行う|
+|  |Merge Join|ソート・マージ結合を行う|
+|  |Hash Join|ハッシュ結合を行う|
 ==== 照合順序 ====