八寿のナレッジベース

ユーザ用ツール

サイト用ツール

トレース:
it技術:データベース:postgresql

差分

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

この比較画面へのリンク

両方とも前のリビジョン前のリビジョン
次のリビジョン		前のリビジョン
it技術:データベース:postgresql [2020/10/18 02:36] – [PostgreSQL12情報] yajuadminit技術:データベース:postgresql [2025/10/16 11:15] (現在) – [複合インデックス] yajuadmin
行 9: 行 9:
  
  
-将来的には追記型アーキテクチャを捨てて、EnterpriseDBが率先して開発している「zHeap」の実装して他のデータベースのようにUNDOログをもつようにしたい。\\+将来的には追記型アーキテクチャを捨てて、EnterpriseDBが率先して開発している「zHeap」の実装して他のデータベースのようにUNDOログをもつようにしたい。→ その後zHeapのメイン開発者が抜けて休止状態\\
 [[https://gihyo.jp/dev/column/newyear/2019/postgresql?page=2|データベースとして，オープンソースとして，コミュニティとして ―石井達夫氏が語るPostgreSQLの強さと課題]] [[https://gihyo.jp/dev/column/newyear/2019/postgresql?page=2|データベースとして，オープンソースとして，コミュニティとして ―石井達夫氏が語るPostgreSQLの強さと課題]]
 ===== 接続ドライバー ===== ===== 接続ドライバー =====
行 55: 行 55:
 |12.0|2019/10/03|[[it技術:データベース:postgresql#postgresql12情報|postgresql12情報]] | |12.0|2019/10/03|[[it技術:データベース:postgresql#postgresql12情報|postgresql12情報]] |
 |13.0|2020/09/24|[[it技術:データベース:postgresql#postgresql13情報|postgresql13情報]] | |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情報]] |
 +|17.0|2024/09/26|[[it技術:データベース:postgresql#postgresql17情報|postgresql17情報]] |
 +|18.0|2025/09/25|[[it技術:データベース:postgresql#postgresql18情報|postgresql18情報]] |
 +==== サポート終了期限(EOL＝End Of Life) ====
 +https://www.postgresql.org/support/versioning/
  
-==== Postgres10情報 ====+^バージョン^初期リリース日^サポート終了期限^ 
 +|18|2025年09月25日|2030年11月7日| 
 +|17|2024年09月26日|2029年11月8日| 
 +|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日| 
 +|11|2018年10月18日|2023年11月9日| 
 +|10|2017年10月5日|2022年11月10日| 
 +|9.6|2016年09月29日|2021年11月11日| 
 + 
 +==== PostgreSQL10情報 ====
 2017-10-05にリリース 2017-10-05にリリース
  
 === 主な新機能 === === 主な新機能 ===
   - IDカラム \\ 行のIDを保存するカラム、一意かつ自動でカウントアップされます。   - IDカラム \\ 行のIDを保存するカラム、一意かつ自動でカウントアップされます。
-  - ネイティブのパーティショニング機能 \\ ひとつのテーブルの実体を分割して扱える+  - ネイティブのパーティショニング機能 \\ ひとつのテーブルの実体を分割して扱える \\ ※[[ https://tech-lab.sios.jp/archives/11693|UPDATE時にパーティションキーが更新されるとエラーになる]]
   - 複数列統計 \\ [[http://www.oracle.com/webfolder/technetwork/jp/obe/11gr1_db/perform/multistats/multicolstats.html|Oracle11gで採用された複数列統計]]相当なもの   - 複数列統計 \\ [[http://www.oracle.com/webfolder/technetwork/jp/obe/11gr1_db/perform/multistats/multicolstats.html|Oracle11gで採用された複数列統計]]相当なもの
   - 並列性の強化 \\ パラレルクエリの改善   - 並列性の強化 \\ パラレルクエリの改善
   - JSONとJSONBの全文検索 \\ JSON型やJSONB型のカラムで全文検索をサポート   - JSONとJSONBの全文検索 \\ JSON型やJSONB型のカラムで全文検索をサポート
   - 論理レプリケーション \\ 特定のテーブルの情報だけをレプリケーションする   - 論理レプリケーション \\ 特定のテーブルの情報だけをレプリケーションする
 +
 +=== 名称変更 ===
 +PostgreSQL10ではいくつかのディレクトリ名や関数名などが変更されています。メンテナンス系のシェルスクリプトや監視ツールで下記ディレクトリ名や関数名などをハードコードされている場合は、PostgreSQL10以降の名称に修正する必要があります。
 +
 +  * ログファイル出力先のディレクトリ名がpg_logからlogへ変更
 +  * WALに関連するディレクトリ／関数／コマンドなどでxlogがwal、locationがlsnに変更
 +  * コミットログの出力先ディレクトリ名がpg_clogからpg_xactへ変更
 +
 +^PostgreSQL10以降で変更されたディレクトリ／関数名などの例^^
 +^PostgreSQL9.6以前の名称^PostgreSQL10以降の名称^
 +|pg_log|log|
 +|pg_xlog|pg_wal|
 +|pg_clog|pg_xact|
 +|pg_current_xlog_location|pg_current_wal_lsn|
 +|pg_xlogdump|pg_waldump|
 +|pg_receivexlog|pg_receivewal|
 +
  
 === 参照 === === 参照 ===
行 81: 行 117:
  
   * JITコンパイルの追加 ［性能］   * JITコンパイルの追加 ［性能］
-  * パーティショニングの様々な改善［機能＋性能］+  * パーティショニングの様々な改善［機能＋性能］\\ ※[[ https://tech-lab.sios.jp/archives/11693|UPDATE時にパーティションキーが更新されると適切な子テーブルに移動される]]
   * 並列実行の様々な改善［性能］   * 並列実行の様々な改善［性能］
   * プロシージャ内でのトランザクション制御をサポート ［機能］   * プロシージャ内でのトランザクション制御をサポート ［機能］
行 92: 行 128:
  
 === 主な新機能 === === 主な新機能 ===
 +[[https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_12_GA_New_Features_ja_20191011-1.pdf|PostgreSQL 12 新機能検証結果 (GA) - pdf]]\\
 [[https://www.postgresql.org/about/press/presskit12/ja/|PostgreSQL 12 プレスキット]]\\ [[https://www.postgresql.org/about/press/presskit12/ja/|PostgreSQL 12 プレスキット]]\\
 [[https://mag.osdn.jp/19/10/04/163000|「PostgreSQL 12」リリース、性能面でさまざまな強化が行われる]]\\ [[https://mag.osdn.jp/19/10/04/163000|「PostgreSQL 12」リリース、性能面でさまざまな強化が行われる]]\\
-[[https://www.publickey1.jp/blog/19/postgresql_12bjson.html|PostgreSQL 12が正式リリース。Bツリー周りの改善による性能向上、JSONパスによる抽出可能など]] +[[https://www.publickey1.jp/blog/19/postgresql_12bjson.html|PostgreSQL 12が正式リリース。Bツリー周りの改善による性能向上、JSONパスによる抽出可能など]]\\ 
-[[https://www.slideshare.net/nttdata-tech/postgresql12-performance-improvement-pluggable-storage-engine-ntt-sawada|PostgreSQL 12は ここがスゴイ！ ～性能改善やpluggable storage engineなどの新機能を徹底解説～ - SlideShare]]+[[https://www.slideshare.net/nttdata-tech/postgresql12-performance-improvement-pluggable-storage-engine-ntt-sawada|PostgreSQL 12は ここがスゴイ！ ～性能改善やpluggable storage engineなどの新機能を徹底解説～ - SlideShare]]\\ 
 +[[https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/partitioning-performance/|技術者に聞く：パーティショニングにおける性能改善への取り組み PostgreSQLインサイド]]\\ 
 +[[https://aws.amazon.com/jp/blogs/news/postgresql-12-a-deep-dive-into-some-new-functionality/|PostgreSQL 12 – いくつかの新機能のご紹介]]
  
  
行 103: 行 142:
   * パーティショニングの機能追加、性能改善   * パーティショニングの機能追加、性能改善
   * テーブルアクセスメソッドに対応   * テーブルアクセスメソッドに対応
 +  * REINDEX CONCURRENTLYが追加、重いロックなしでインデックスを再構築可能
 +  * WITH句の共通テーブル式(CTE)で同じCTEが2回以上ある場合に[[https://www.sraoss.co.jp/tech-blog/pgsql/pg12-cte/|高速化]]
  
 === 注意 === === 注意 ===
-  * WITH OIDが使えなくなった。WITH OIDSがあるテーブルがあるとpg_upgradeが実行できない</wrap>+  * WITH OIDが使えなくなった。WITH OIDSがあるテーブルがあるとpg_upgradeが実行できない
   * recovery.confはなくなりpostgresql.confに統合   * recovery.confはなくなりpostgresql.confに統合
 +
 +=== 対処方法 ===
 +[[https://www.slideshare.net/nttdata-tech/postgresql12-performance-improvement-pluggable-storage-engine-ntt-sawada|PostgreSQL 12は ここがスゴイ！ ～性能改善やpluggable storage engineなどの新機能を徹底解説～ - SlideShare]]
 +
 +  * WITH OIDSを使わなくても良い設計を検討する
 +  * ALTER TABLE ... SET WITHOUT OIDSでOIDSを取り除く
 +  * recovery.confに書いていたパラメータをpostgresql.confに書くようにする
 +  * リカバリ時はrecovery.signal、スタンバイ時はstandby.signal を置くようにする
 +  * recovery_target_XXXは設定ファイルに必ず一つになるようにすること
 ==== PostgreSQL13情報 ==== ==== PostgreSQL13情報 ====
 2020-09-24にリリース 2020-09-24にリリース
行 113: 行 163:
 [[https://www.postgresql.org/about/press/presskit13/ja/|PostgreSQL 13 プレスキット]]\\ [[https://www.postgresql.org/about/press/presskit13/ja/|PostgreSQL 13 プレスキット]]\\
 [[https://www.sraoss.co.jp/tech-blog/pgsql/pg13report/|PostgreSQL 13 検証報告]]\\ [[https://www.sraoss.co.jp/tech-blog/pgsql/pg13report/|PostgreSQL 13 検証報告]]\\
-[[https://news.mynavi.jp/article/20200525-1040900/|PostgreSQL 13で登場する新機能まとめ]]+[[https://news.mynavi.jp/article/20200525-1040900/|PostgreSQL 13で登場する新機能まとめ]]\\ 
 +[[https://qiita.com/nuko_yokohama/items/e253dd2619c639558a23|PostgreSQL 13がやってくる！(6) - psqlの改善あれこれ]]\\ 
 +[[https://www.slideshare.net/nttdata-tech/postgresql13-pg-stat-statements-nttdata-fujii|PostgreSQL 13でのpg_stat_statementsの改善について - SlideShare]]\\ 
 +[[https://www.slideshare.net/nttdata-tech/postgresql13-pg-stat-statements-nttdata-fujii|PostgreSQL 13でのpg_basebackupの改善について - SlideShare]]\\ 
 +[[https://www.slideshare.net/nttdata-tech/postgresql13-replication-nttdata-fujii|PostgreSQL 13でのレプリケーション関連の改善について - SlideShare]]
  
   * B-Treeインデックスの性能向上   * B-Treeインデックスの性能向上
行 123: 行 177:
   * pg_rewindの拡張   * pg_rewindの拡張
   * SQL機能の追加   * SQL機能の追加
 +
 +==== PostgreSQL14情報 ====
 +2021-09-30にリリース
 +
 +=== 主な新機能 ===
 +[[https://www.postgresql.org/about/press/presskit14/ja/|PostgreSQL 14 プレスキット]]\\
 +[[https://www.sraoss.co.jp/tech-blog/pgsql/pg14report/|PostgreSQL 14 検証報告]]\\
 +[[https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2021/08/pg14_report_20210818.pdf|PostgreSQL14 検証レポート- pdf]]
 +
 +  * Btreeインデックスの肥大化防止
 +  * 式に対する拡張統計
 +  * LZ4による列のTOAST圧縮
 +  * postgres_fdwの拡張
 +  * ロジカルレプリケーションの改善
 +  * マルチ範囲型
 +  * 再帰CTE問い合わせにおけるSEARCH／CYCLE句のサポート
 +  * アイドルセッションのタイムアウト機能
 +  * 新システムロールpg_read_all_data/pg_write_all_data
 +  * COPY進捗表示、WAL書き出しやロジカルレプリケーションの統計表示
 +  * pg_amcheckコマンド
 +  * ANALYZEコマンドのパフォーマンス改善により大幅に高速化
 +
 +==== PostgreSQL15情報 ====
 +2022-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情報 ====
 +2023-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ビルド
 +
 +==== PostgreSQL17情報 ====
 +2024-09-26にリリース
 +
 +=== 主な新機能 ===
 +[[https://www.sraoss.co.jp/tech-blog/pgsql/pg17report/|PostgreSQL 17検証報告]]\\
 +[[https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2024/10/pg17_report_20241021.pdf|PostgreSQL 17 検証レポート - pdf]]
 +
 +  * 性能向上
 +    * VACUUM性能改善
 +    * COPY性能向上
 +    * ストリームI/O対応
 +    * 各種プランナ改善
 +  * SQL機能
 +    * SQL/JSON対応の拡充
 +    * MERGE文の拡張
 +    * COPY FROM に ON_ERROR オプション追加
 +  * ロジカルレプリケーション機能追加
 +    * pg_upgrade 対応の改善
 +    * フェイルオーバに対応
 +    * pg_createsubscriberコマンド追加
 +  * パーティショニング機能
 +    * 排他制約に対応
 +    * IDENTITY列に対応
 +  * 運用管理
 +    * インクリメンタルバックアップ
 +    * 新たな定義済みロール
 +    * 新たなモニタリングビュー
 +    * pg_dump の –filterオプション
 +
 +==== PostgreSQL18情報 ====
 +2025-09-25にリリース
 +
 +=== 主な新機能 ===
 +[[https://www.sraoss.co.jp/tech-blog/pgsql/pg18report/|PostgreSQL 18検証報告]]\\
 +[[https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2025/09/pg18_report_20250918-1.pdf|PostgreSQL 18 検証レポート - pdf]]
 +
 +  * 性能向上
 +    * 非同期I/O
 +    * Btreeインデックスのスキップスキャン
 +    * プランナ改善（WHERE句内にOR条件がある場合の最適化、ほか）
 +    * GINインデックスの並列作成
 +    * 大量のテーブルアクセス時のロック処理の改善
 +    * 組み込み照合順序pg_unicode_fast
 +  * メジャーバージョンアップ
 +    * プランナ統計情報の移行
 +    * チェック処理の並列化オプションの追加
 +    * --swapオプションの追加
 +  * SQL機能
 +    * 仮想生成列の追加
 +    * 更新系DMLのRETURNING句へOLD, NEWの追加
 +    * UUID v7関数の追加
 +    * 非決定論的な照合順序利用時のLIKE句のサポート
 +    * CASEFOLD関数の追加
 +    * 各種制約の追加（WITHOUT OVERLAPS、ほか）
 +    * ラージオブジェクトに対するデフォルト権限設定のサポート
 +  * セキュリティ機能
 +    * TLS v1.3 暗号スイートの追加
 +    * postgres_fdw、dblinkへのSCRAM認証のパススルーオプションの追加
 +  * 運用管理
 +    * プランナ統計情報のダンプ／リストア
 +    * EXPLAIN文の強化
 +    * pg_stat_all_tablesへのVACUUM、ANALYZE総実行時間の追加
 +    * 接続単位でのI/O・WAL利用量の取得
 +    * 論理レプリケーション時の書き込み衝突の情報詳細化
 ===== 設定について ===== ===== 設定について =====
 postgresql.confの設定値は、下記SQLで context の値により設定反映のタイミングが違う。 postgresql.confの設定値は、下記SQLで context の値により設定反映のタイミングが違う。
行 236: 行 435:
  
 === 実行されたクエリを記録 === === 実行されたクエリを記録 ===
-調査の時に行うくらい。実行するSQLが多いので運用時はコメントアウトにしている。+調査の時に行うくらい。実行するSQLが多いので運用時はコメントアウトにしている。\\ 
 +<wrap em>但し、バインド変数の値までは取れない。またSQL文そのものに文法エラーなどがあり実行されなかった場合も出力されない。</wrap>
  
 <code .conf postgresql.conf> <code .conf postgresql.conf>
行 254: 行 454:
 search_path = 'test,"$user", public' # schema names search_path = 'test,"$user", public' # schema names
 </code> </code>
 +==== psqlの使い方 ====
 +psqlの機能やの使い方を紹介しているスライド資料\\
 +[[https://event.ospn.jp/slides/OSC2022_OnlineKyoto/OSC-kyoto-psql.pdf|psqlの使い方 - pdf]]
 +
 ==== psqlのパスワード入力省略 ==== ==== psqlのパスワード入力省略 ====
 パスワード入力なしでpsqlを実行するには、下記フォルダの「pgpass.conf」にパスワードを設定しておく。 パスワード入力なしでpsqlを実行するには、下記フォルダの「pgpass.conf」にパスワードを設定しておく。
行 332: 行 536:
  
 ===== エクスポート ===== ===== エクスポート =====
 +pg_dumpツールを使用する場合、保存形式がカスタム形式と平文形式があります。\\
 +pg_restoreツールは保存形式がカスタム形式のみ対応しています。
 +
 +[[https://qiita.com/jun1s/items/8ba778b91258c33396e8|[PostgreSQL]pg_dumpとpg_restoreを用いたバックアップとリストア]]
 +
 +=== バックアップ ===
 +^保存形式^バックアップコマンド^圧縮^データのみ^スキーマのみ^テーブル指定^
 +|カスタム形式|pg_dump -Fc|〇|〇|〇|〇|
 +|平文形式|pg_dump -Fp(省略可)|×|〇|〇|〇|
 +
 +=== リストア ===
 +^保存形式^リストアツール^データのみ^スキーマのみ^テーブル指定^SQLに変換^TOCの確認 ※2^他DBへの移行^
 +|カスタム形式|pg_restore|〇|〇|〇|〇|〇|〇※3|
 +|平文形式|psql等SQL実行環境|△※1|△※1|△※1|－|×|〇|
 +
 +  * ※1…バックアップ時に選択した対象を全てリストアする
 +  * ※2…TOC=Table Of Contents。そのバックアップファイルにどのようなデータが格納されているかを一覧形式で確認したり、一覧から選んだデータだけリストアできる。
 +  * ※3…SQLに変換してから行う。変換時に標準SQLオプションを指定する事ができる為、平文形式より柔軟性が高い。
 ==== 全体 ==== ==== 全体 ====
 pg_dumpall pg_dumpall
  
 ==== スキーマ単位 ==== ==== スキーマ単位 ====
-pg_dump -h ホスト名 -p ポート番号 DB名+pg_dump -n スキーマ名 
 + 
 +<code bat スキーマデータ取得.bat> 
 +@echo off 
 + 
 +SET PGPASSWORD=test 
 +pg_dump -U test -n foo > foo.dump 
 +</code>
  
 === リストア === === リストア ===
-pg_restoreは、pg_dumpによってアーカイブされた平文形式以外のアーカイブファイルを使って、PostgreSQLデータベースをリストアするためのユーティリティ+平文形式の場合、psqlコマンドでダンプファイルを指定してリストアする。 
 +<code> 
 +psql -U test < foo.dump 
 +</code>
 ==== テーブル単位 ==== ==== テーブル単位 ====
 tオプションを指定する。\\ tオプションを指定する。\\
行 349: 行 581:
 pg_dump -T (テーブル名) pg_dump -T (テーブル名)
  
-==== スキーマ単位 ==== +
-sオプションを指定する。\\ +
-pg_dump -s (スキーマ名)+
  
  
 ==== 条件指定 ==== ==== 条件指定 ====
 COPYコマンドを使用する。\\ COPYコマンドを使用する。\\
-TOは、絶対パスのみで相対パスは「relative path not allowed for COPY to file」エラーになる。+TOは、絶対パスのみで相対パスは「relative path not allowed for COPY to file」エラーになる。\\ 
 +dumpファイルは、テキスト形式でタブ区切りでNULL文字が「\N」で出力される。
  
 <code> <code>
-COPY  (SELECT * FROM sample_table WHERE id > 10000) TO 'E:/work/tmp/sample_dump';+COPY  (SELECT * FROM sample_table WHERE id > 10000) TO 'E:/work/sample_table.dump';
 </code> </code>
  
行 367: 行 598:
   * [[https://qiita.com/cyborg__ninja/items/99efcb5b62a4cef2f156|PostgreSQLのCSV出力(Export)方法]]   * [[https://qiita.com/cyborg__ninja/items/99efcb5b62a4cef2f156|PostgreSQLのCSV出力(Export)方法]]
  
 +=== writing: Permission denied === 
 +PostgreSQLのユーザーで実行されるため、出力先がPostgreSQLのユーザーで書き込めるようにする。\\ 
 +フォルダにNETWORK SERVICEユーザーを追加して「フル コントロール」にする。 
 +{{:it技術:データベース:psql使用フォルダ権限.png|}}
 ===== インポート ===== ===== インポート =====
 PostgreSQLのCSVインポートする場合、COPY コマンドで取り込む。\\ PostgreSQLのCSVインポートする場合、COPY コマンドで取り込む。\\
行 487: 行 721:
        CONNECTION LIMIT = -1;        CONNECTION LIMIT = -1;
 </code> </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>
 +
 +=== PostgreSQL 18以降の場合 ===
 +PostgreSQL 18の新機能「B-treeインデックスのスキップスキャン」により、先頭以外からでもインデックスが効くようになる。
 +
 +[[https://future-architect.github.io/articles/20251014a/|PostgreSQL 18の新機能「B-treeインデックスのスキップスキャン」]]
 +==== 実行計画の取得 ====
 +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|ハッシュ結合を行う|
  
 ==== 照合順序 ==== ==== 照合順序 ====
行 569: 行 864:
 COPY (SELECT * FROM fuga WHERE id = :id) TO :inputpath; COPY (SELECT * FROM fuga WHERE id = :id) TO :inputpath;
 </code> </code>
 +==== パフォーマンス向上 ====
 +  * [[https://techracho.bpsinc.jp/hachi8833/2020_04_16/79219|PostgreSQL: 「OR」を避けてパフォーマンスを向上させよう]]
 +  * [[https://blog.honjala.net/entry/2016/11/19/021057|PostgreSQLでpg_bigmを使って中間一致like検索を高速化する]]
 +
 ==== その他 ==== ==== その他 ====
   * postgresql.confのlc_messagesを「Japanese_Japan.932」から「C」にすることでエラーメッセージが文字化けしなくなる。   * postgresql.confのlc_messagesを「Japanese_Japan.932」から「C」にすることでエラーメッセージが文字化けしなくなる。
it技術/データベース/postgresql.1602956213.txt.gz · 最終更新: 2020/10/18 02:36 by yajuadmin