ユーザ用ツール

サイト用ツール


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

PostgreSQL

概要

読み方は、ポストグレス・キューエル。Ingresの次版(Post)としてPostgresとなった。QLはQuery Languageの略。

PostgreSQLは、追記型アーキテクチャを採用している。データの変更があっても元のレコードを物理的に消さずに、新しい行を追加して、元のレコードを無効マークとします。
不要領域を再利用可能な状態にするには、VACUUMコマンドを使います。

PostgreSQLのお勉強

将来的には追記型アーキテクチャを捨てて、EnterpriseDBが率先して開発している「zHeap」の実装して他のデータベースのようにUNDOログをもつようにしたい。
データベースとして,オープンソースとして,コミュニティとして ―石井達夫氏が語るPostgreSQLの強さと課題

接続ドライバー

PostgreSQL OLDDBドライバーは、遅い上にPostgreSQLのnumeric型フィールド値がうまく取得できないなど問題があるため、ODBCドライバーを使用すること。

ちなみに別のOLDDBドライバーであるPM PostgreSQL OLEDBを使用してみたが、速度も遅くClassic.ASP(VB)のCIntエラーが出るなど使えなかった。

OLDDBドライバーを使用してシステムでは、遅いということで調査したところ1つのSQLで10msの処理が100ms程度かかっていた。ODBCドライバーに変更したところ正常な速度となった。

ODBCドライバー設定

psqlodbc_09_00_0200.zip を解凍して、指示通りにインストールする。
管理ツールの「ODBCデータソースアドミニストレータ」にてシステムDNS
PostgreSQL35W PostgreSQL Unicode
サーバー名 localhost
データベース名 db_test
ユーザー名 fuga
パスワード hoge

接続文字列変更

test\Global.asa の接続をODBC用に変更する。

Global.asa
'Session("ConnectionString") = "Provider=PostgreSQL OLE DB Provider;Data Source=localhost;Location=db_test;User ID=fuga;Password=hoge"
↓
Session("ConnectionString") = "DSN=PostgreSQL35W;Server=localhost;Database=db_test;UID=fuga;PWD=hoge;Port=5432;"

NPGSQL ver3にした場合

接続文字列の「Preload Reader=true」が不要になりました。あると例外エラーになる。

バージョン

PostgreSQL wiki
現在の9.6の最新版は9.6.17(リリース日:2020-02-13)
https://www.postgresql.org/docs/9.6/release-9-6-17.html

バージョンリリース主な変更点
9.22012/09/10インデックスオンリースキャン, カスケードレプリケーション, JSON型, 範囲型
9.32013/09/09マテリアライズドビュー, 外部テーブルへの書き出し, イベントトリガ, データページ・チェックサム, LATERAL句
9.42014/12/18JSONB型, SQLからのサーバー設定の変更(ALTER SYSTEM), レプリケーションスロット
9.52016/01/07UPSERT機能, ALTER TABLE tablename ENABLE ROW LEVEL SECURITYコマンド, ブロックレンジインデックス(BRIN)
9.62016/09/29同期レプリケーション機能の強化(「remote_apply」モード), PostgreSQL間のデータ連携ドライバー(「postgres_fdw」)の強化(リモート下にあるサーバーにおいても実行可能となる
10.02017/10/05postgresql10情報
11.02018/10/18postgresql11情報
12.02019/10/03postgresql12情報
13.02020/09/24postgresql13情報
14.02021/09/30postgresql14情報
15.02022/10/06postgresql15情報
16.02023/09/14postgresql16情報

サポート終了期限(EOL=End Of Life)

https://www.postgresql.org/support/versioning/

バージョン初期リリース日サポート終了期限
162023年09月14日2028年11月9日
152022年10月13日2027年11月11日
142021年09月30日2026年11月12日
132020年09月24日2025年11月13日
122019年10月3日2024年11月14日
112018年10月18日2023年11月9日
102017年10月5日2022年11月10日
9.62016年09月29日2021年11月11日

PostgreSQL10情報

2017-10-05にリリース

主な新機能

  1. IDカラム
    行のIDを保存するカラム、一意かつ自動でカウントアップされます。
  2. ネイティブのパーティショニング機能
    ひとつのテーブルの実体を分割して扱える
    UPDATE時にパーティションキーが更新されるとエラーになる
  3. 複数列統計
    Oracle11gで採用された複数列統計相当なもの
  4. 並列性の強化
    パラレルクエリの改善
  5. JSONとJSONBの全文検索
    JSON型やJSONB型のカラムで全文検索をサポート
  6. 論理レプリケーション
    特定のテーブルの情報だけをレプリケーションする

名称変更

PostgreSQL10ではいくつかのディレクトリ名や関数名などが変更されています。メンテナンス系のシェルスクリプトや監視ツールで下記ディレクトリ名や関数名などをハードコードされている場合は、PostgreSQL10以降の名称に修正する必要があります。

  • ログファイル出力先のディレクトリ名がpg_logからlogへ変更
  • WALに関連するディレクトリ/関数/コマンドなどでxlogがwal、locationがlsnに変更
  • コミットログの出力先ディレクトリ名がpg_clogからpg_xactへ変更
PostgreSQL10以降で変更されたディレクトリ/関数名などの例
PostgreSQL9.6以前の名称PostgreSQL10以降の名称
pg_loglog
pg_xlogpg_wal
pg_clogpg_xact
pg_current_xlog_locationpg_current_wal_lsn
pg_xlogdumppg_waldump
pg_receivexlogpg_receivewal

参照

PostgreSQL11情報

2018-10-18にリリース

主な新機能

PostgreSQL 11 検証報告

  • JITコンパイルの追加 [性能]
  • パーティショニングの様々な改善[機能+性能]
    UPDATE時にパーティションキーが更新されると適切な子テーブルに移動される
  • 並列実行の様々な改善[性能]
  • プロシージャ内でのトランザクション制御をサポート [機能]
  • 認証でSCRAMチャンネルバインドをサポート[セキュリティ]
  • ALTER TABLE .. ADD COLUMN の性能改善 [性能]
  • ウィンドウ関数の拡張[機能]

PostgreSQL12情報

2019-10-03にリリース

主な新機能

PostgreSQL 12 新機能検証結果 (GA) - pdf
PostgreSQL 12 プレスキット
「PostgreSQL 12」リリース、性能面でさまざまな強化が行われる
PostgreSQL 12が正式リリース。Bツリー周りの改善による性能向上、JSONパスによる抽出可能など
PostgreSQL 12は ここがスゴイ! ~性能改善やpluggable storage engineなどの新機能を徹底解説~ - SlideShare
技術者に聞く:パーティショニングにおける性能改善への取り組み PostgreSQLインサイド
PostgreSQL 12 – いくつかの新機能のご紹介

  • JSON Pathに対応
  • 生成列(式で計算される列)に対応
  • 各種インデックスの機能追加、性能改善
  • パーティショニングの機能追加、性能改善
  • テーブルアクセスメソッドに対応
  • REINDEX CONCURRENTLYが追加、重いロックなしでインデックスを再構築可能
  • WITH句の共通テーブル式(CTE)で同じCTEが2回以上ある場合に高速化

注意

  • WITH OIDが使えなくなった。WITH OIDSがあるテーブルがあるとpg_upgradeが実行できない
  • recovery.confはなくなりpostgresql.confに統合

対処方法

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情報

2020-09-24にリリース

主な新機能

PostgreSQL14情報

2021-09-30にリリース

主な新機能

PostgreSQL 14 プレスキット
PostgreSQL 14 検証報告
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にリリース

主な新機能

PostgreSQL 15検証報告
PostgreSQL15 検証レポート - pdf
アプリケーションエンジニアから見たPostgreSQL15 の新機能

  • Merge文のサポート
  • 正規表現関数の追加
  • NOT IN句の改善
  • ソートの性能改善
  • ウィンドウ関数の性能改善
  • psql \copyの性能改善
  • zstandard圧縮サポート
  • 先読みによるリカバリ性能改善
  • SQL/JSON対応の拡張
  • 多重範囲型への集約
  • ロジカルレプリケーションの機能拡張
  • 呼び出し元権限で実行されるビュー
  • モジュールによるWALアーカイブ
  • JSONLOG形式
  • モニタリングビューの追加
  • ロジカルレプリケーションの機能拡張
  • パラレルクエリの強化
  • バージョン非互換対応(新機能ではないけど)
    • PublicスキーマのCreate権限がデフォルトからなくなる

PostgreSQL16情報

2023-09-14にリリース

主な新機能

PostgreSQL 16検証報告
篠田の虎の巻「PostgreSQL 16 Beta 1 新機能検証結果」公開!

  • 性能向上
    • パラレルクエリ対応追加
    • ウィンドウ関数の最適化
    • 同時データ投入の性能向上
    • SIMD CPUアクセラレータ対応
  • SQL機能
    • SQL/JSON 対応
    • 整数リテラル表現の追加
    • ICU照合順序の拡張
    • ロジカルレプリケーション機能追加
    • スタンバイ上でのパブリケーション
    • トランザクションの並列適用
    • 双方向ロジカルレプリケーション
  • クライアント機能
    • libpqロードバランス
    • psqlで拡張プロトコル対応
    • pg_dump圧縮オプション追加
  • 運用管理
    • 新たなモニタリング項目
    • 新たな定義済みロール
    • ページ凍結の改善
    • VACUUMリングバッファ指定
    • Mesonビルド

設定について

postgresql.confの設定値は、下記SQLで context の値により設定反映のタイミングが違う。

-- postgresユーザーで実行
SELECT name, context FROM pg_settings;
設定反映のタイミング
context説明
不可(internal)ユーザー設定変更が出来ない
起動(postmaster)PostgreSQLの再起動で反映される
再読み込み(signup)pg_ctl reload でも反映できる
スーパーユーザーのみ(suset)スーパーユーザーによるSET文でも反映できる
いつでも(user)一般ユーザーによるSET文でも反映できる

サーバー上の管理者権限で「pg_ctl reload」コマンドでpostgresql.confの設定値を反映

>pg_ctl reload -D "F:\Program Files\PostgreSQL\9.6\data"
サーバにシグナルを送信しました

他ホストから接続するための設定変更

コメントを外す。「localhost」から「*」に変更する。これをしないとlocalhostしか接続できない。
他ホストから接続するための設定

postgresql.conf
listen_addresses = '*'		# what IP address(es) to listen on;
port = 5432			# (change requires restart)

※変更後は、PostgreSQLサービス再起動

接続できるクライアントを設定する

pg_hda.conf に設定追加する。

全てのクライアントPCからの接続を許可

pg_hda.conf
# IPv4 local connections:
host all all 0.0.0.0/0 md5

クライアントPCからの接続に制限を付ける

pg_hda.conf
# IPv4 local connections:
host    all             all             192.168.64.0/24         md5
host    all             all             192.168.71.0/24         md5

※変更後は、PostgreSQLサービス再起動は不要

※認証方式(https://www.postgresql.jp/document/9.4/html/auth-methods.html)

  • trust
    任意のロール名でパスワードなしで接続可能
  • md5
    パスワード認証

ログ出力設定変更

logging_collectorを「off」から「on」にして、pg_logフォルダにログ出力させる。
障害発生に備えて設定すべき3つのログ関連パラメーター

postgresql.conf
logging_collector = on		# Enable capturing of stderr and csvlog
log_line_prefix='[%t] %u %d %p[%l] '	# special values:

※変更後は、PostgreSQLサービス再起動またはpg_ctl reloadで反映

ログ出力例
[2019-04-25 14:37:08 JST]  7496[1] LOG:  database system was shut down at 2019-04-25 14:37:06 JST
[2019-04-25 14:37:08 JST]  7496[2] LOG:  MultiXact member wraparound protections are now enabled
[2019-04-25 14:37:08 JST]  6592[3] LOG:  database system is ready to accept connections
[2019-04-25 14:37:08 JST]  3240[1] LOG:  autovacuum launcher started

postgresql.conf エラーログの設定 Windowsの場合

log_line_prefix = '%e: %t [%p]: [%l-1] user = %u,db =%d,remote = %r app = %a '
log_line_prefix
エスケープ効果セッションのみ
%aアプリケーション名 例 psql または [unknown]
%uユーザ名
%dデータベース名
%r遠隔ホスト名、またはIPアドレス、およびポート番号
%h遠隔ホスト名、またはIPアドレス
%pプロセス識別子×
%tミリ秒無しのタイムスタンプ×
%mミリ秒付きタイムスタンプ×
%iコマンドタグ。セッションの現在のコマンド種類
%eSQLSTATE エラーコード×
%cセッションID。下記参照×
%l各セッションまたは各プロセスのログ行の番号。1から始まります。×
%sプロセスの開始タイムスタンプ×
%v仮想トランザクションID(backendID/localXID)×
%xトランザクションID (未割り当ての場合は0)×
%q何も出力しません。 非セッションプロセスではこのエスケープ以降の出力を停止します。 セッションプロセスでは無視されます。×

クライアントからの接続・切断を記録

ひとつのデータベースを複数のアプリケーションで参照する場合、以下の設定を行っておくと接続元のIPが記録されるため、どこからの接続が多いかがログから判断できます。→システム監査用途

デフォルトでは off となっています。
有効にする場合は on に設定してください。

postgresql.conf
log_connections = on
#log_disconnections = off
ログ出力例
[2020-03-28 15:45:06 JST] [unknown] [unknown] 8512[1] LOG:  connection received: host=10.55.3.3 port=62717
[2020-03-28 15:45:06 JST] postgres postgres 8512[2] LOG:  connection authorized: user=postgres database=postgres

実行されたクエリを記録

調査の時に行うくらい。実行するSQLが多いので運用時はコメントアウトにしている。
但し、バインド変数の値までは取れない。またSQL文そのものに文法エラーなどがあり実行されなかった場合も出力されない。

postgresql.conf
log_statement = ‘all’

PostgreSQLで実行されたクエリからバグを探してみた(Windows編)

接続子の優先設定変更

postgresql.confの下記設定を変更することによって、スキーマ名未設定でSQL実行時に選択されるスキーマの優先度を変更できる。

postgresql.conf 530
#search_path = '"$user", public'	# schema names

search_pathに設定されている値の左から順に選択されるので「test」スキーマを自動的に選択するためには以下のように設定する。

postgresql.conf 530
search_path = 'test,"$user", public'	# schema names

psqlの使い方

psqlの機能やの使い方を紹介しているスライド資料
psqlの使い方 - pdf

psqlのパスワード入力省略

パスワード入力なしでpsqlを実行するには、下記フォルダの「pgpass.conf」にパスワードを設定しておく。 %APPDATA%\postgresql\pgpass.conf

pgpass.conf
localhost:5432:*:postgres:(パスワード)

もう1つのやり方として、セキュリティ上は非推奨であるがバッチ内に環境変数(PGPASSWORD)を設定した上で実行する。

SET PGPASSWORD=(パスワード)
psql -U (ユーザー) -f xxxxx.sql 

psqlのスクリプトエラー無視

postgresqlの特性上、1度エラーが出ると以降のSQLは全てエラー(commit/rollbackするまで)になります。
エラーは無視して、OKなSQLだけ実行したい場合、ファイルの先頭に下記を記述する。

※COPY句のCSV登録時のエラーには関係しない

\set ON_ERROR_ROLLBACK on

ビュー定義

自動変換

ビュー作成時に整形したりコメントを付けたとしても、ビューに登録された段階で自動変換されて型変換が付いたり整形が崩れたりコメントが消去されてしまう。

型変換エラー

ビューで「cannot change data type of view column」で間違っていないのに登録できない場合、ビューを削除して再作成すると登録できるようになる。

ユーザ定義関数

動的クエリ

「EXECUTE sql;」として、動的クエリを実行させる。また、「RETURN QUERY」としてテーブルを返す。
PostgreSQL めも - ストアドプロシージャ

SQLの文字列の値セットにformat関数で指定している。
9.4.1. format
format指示子の出力を生成するのに使用されるフォーマット変換の型。 以下の型がサポートされています。

  • 「%s」は引数の値を単純文字列にフォーマットします。 NULL値は空文字列として扱われます。
  • 「%I」は、必要とされれば二重括弧で括られたSQL識別子として引数値を取り扱います。 NULL値はエラーです。
  • 「%L」は引数値をSQLリテラルとして引用します。NULL値は引用符無しでNULL文字列として表示されます。
CREATE OR REPLACE FUNCTION wiremodule(_carline TEXT, _part TEXT, _module TEXT) 
RETURNS TABLE(drawingno text, wirename text) AS $$
DECLARE
  i INTEGER;
  SQL TEXT;
  cat TEXT; 
BEGIN
  SQL := format('SELECT drawingno::text, wirename::text FROM wire WHERE carline=%L AND part=%L AND (' , _carline, _part);
 
  cat := '';
  FOR i IN 1..4 LOOP
     SQL := SQL || cat || format('functionmodule%s=%L', i , _module);
     cat := ' OR ';
  END LOOP;
  SQL := SQL || ')';
 
  RETURN QUERY EXECUTE SQL;
 
END;
$$ LANGUAGE 'plpgsql';

デバッグ

デバッガ画面でトレースは出来るが、不安定である(ステップアウトで応答が返らない)。

従来のメッセージ出力によるデバッグ方法もある。

エクスポート

全体

pg_dumpall

スキーマ単位

pg_dump -h ホスト名 -p ポート番号 DB名

リストア

pg_restoreは、pg_dumpによってアーカイブされた平文形式以外のアーカイブファイルを使って、PostgreSQLデータベースをリストアするためのユーティリティ

テーブル単位

tオプションを指定する。
pg_dump -t (テーブル名)

pg_dumpで複数のテーブルのダンプを取る方法

逆に除外する場合は、英大文字のTオプションを指定する。
pg_dump -T (テーブル名)

スキーマ単位

sオプションを指定する。
pg_dump -s (スキーマ名)

条件指定

COPYコマンドを使用する。
TOは、絶対パスのみで相対パスは「relative path not allowed for COPY to file」エラーになる。
dumpファイルは、テキスト形式でタブ区切りでNULL文字が「\N」で出力される。

COPY  (SELECT * FROM sample_table WHERE id > 10000) TO 'E:/work/sample_table.dump';

TOをFROMにするとインポートできる。

writing: Permission denied

PostgreSQLのユーザーで実行されるため、出力先がPostgreSQLのユーザーで書き込めるようにする。
フォルダにNETWORK SERVICEユーザーを追加して「フル コントロール」にする。

インポート

PostgreSQLのCSVインポートする場合、COPY コマンドで取り込む。
その際には、UTF8 のテキストファイルは BOM 無しとする。
※BOM有りだと1行目でvarchar(n)で長すぎます。「先頭列名:データ」でエラーとなる。

  • NULLは、CSVモードでは引用符のない空値を指定する。
  • 空白をNULL扱いにする場合、「NULL AS ''」を追加する。
  • 先頭行にヘッダーがある場合、「HEADER」を追加する。
  • CSV側とPostgre側の列順が違う場合、列順を指定する。
  • 引用符がデータ内に含まれている場合、データの中に発生しそうにないオプションでバックスペース(x08)を引用符として指定する。quote e'\x08'
// ファイル名は絶対パスで、'\', '/' または '\\' に置換する
SET CLIENT_ENCODING TO 'UTF8';
DELETE FROM tablename;
COPY tablename FROM pathName WITH CSV;
カラム指定なしの例
copy tablename FROM pathName WITH encoding 'UTF8' CSV HEADER NULL AS '';

Oracleは後で列追加すると後ろに付く。PostgreSQLは最初から正しい順序でテーブルを作成した場合。

カラム指定ありの例
copy tablename(col1,col3,col4,col2) FROM pathName WITH encoding 'UTF8' CSV HEADER NULL AS '';

注意点

null のところが空欄でセット

CSVによるデータ移行でCOPY句に「NULL AS ''」オプションを付けていたが、null のところが空欄でセットされてしまっていた。

原因は、単に文字列が空の場合に二重引用符で囲まれていたため。
対応は、空欄は二重引用符を除去する置換処理を追加する。

COPY句でFORCE_NULLを指定すれば、2重引用符のみをNULLで登録できる。 https://www.postgresql.jp/document/11/html/sql-copy.html

WITHの書き方をこれまでと変える必要があるのと、FORCE_NULLの時にカラムを指定する必要がある。

\COPY tablename(col1,col3,col4,col2) FROM pathName WITH encoding 'UTF8' CSV HEADER NULL AS '';
                                                     ↓
\COPY tablename(col1,col3,col4,col2) FROM pathName WITH(ENCODING 'UTF8',FORMAT CSV,HEADER ON,NULL '',FORCE_NULL(col1,col3,col4,col2));

COPYで読み込みエラー

「読み取り用にオープンできませんでした: Permission denied」が出た場合、参照フォルダのプロパティのセキュリティタブにて“Network Service”アカウント、または、“Everyone”アカウントを追加する。

参照

高速化

インデックスを張らない状態でロードする
初期ロードの際には、インデックスを張ってからロードするよりも、張らずにロードして後から CREATE INDEX したほうが速い場合が多いようです。 このとき、主キー (PRIMARY KEY)、一意性制約 (UNIQUE)、排他制約 (EXCLUDE) も暗黙的にインデックスが追加されることに注意してください. 外部キーの追加もロード後のほうが良いでしょう。

バッチ化

-f オプションでファイル名を指定する。
psqlのパスワード入力省略

psqlオプション

xxxxx.bat
psql -U hostname -d databasename -f xxxxx.sql
xxxxx.sql
DELETE FROM tablename;
COPY tablename FROM pathname WITH encoding 'UTF8' CSV;

Tips

全テーブル件数の取得

統計情報から取得しているので、おおよその件数が取得できる。
統計情報を手動で行うコマンド「VACUUM ANALYZE」を実行してからなら、正確な件数が取得できる。

SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE schemaname='test'

PostgreSQLでテーブルの行数を速く取得する方法

文字列結合

SELECT句で Null文字列を含むカラムを連結すると、全体が Null になります。
これを回避するためには、カラムごとに Null の場合の置換を指定することで対処可能です。
[PostgreSQL] PostgreSQL の SELECT句で Null文字列を連結する方法

SELECT ok_column || '-' || COALESCE((null_column, '') FROM foo;

シーケンス作成

serial を使ったカラムの作成すると、自動でシーケンスが作成される。
serial ではないカラムで別途作成したシーケンスを適用するには、DEFAULTにシーケンスをセットする。

id INTEGER DEFAULT NEXTVAL('xxxxx_id_seq') NOT NULL,

[PostgreSQL]serial と 手動でのシーケンス作成の違い

データベース作成

デフォルトの設定が「C」となっている場合に違う照合順序「Japanese_Japan.932」に指定するには、「TEMPLATE = template0」を追記する。

CreateDatabase.sql
CREATE DATABASE "FUGA"
  WITH OWNER = postgres
       TEMPLATE = template0
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'Japanese_Japan.932'
       LC_CTYPE = 'Japanese_Japan.932'
       CONNECTION LIMIT = -1;

複合インデックス

複合インデックスは、キーの先頭から途中までのカラムが指定されていればインデックスが使われる。

PostgreSQLの場合、インデックスが使われる為には、先頭のキーが必ず必要になる。
複合インデックスは、構成列のどの列を指定すれば選択候補に挙がるのか。

あるテーブルのカラム(C1, C2, C3) に対して複合インデックスを貼っている状況を考えます。
C1, C2, C3 の順で順序付けされています。

C1C2C3複合インデックス利用可不可
×
××
×可 ※C1のみ、C3は無視
×可 ※table scanが選択される可能性高い
××可 ※table scanが選択される可能性高い
××可 ※table scanが選択される可能性高い
×××不可

主キーが複数の場合のインデックスにはデフォルトのb-treeインデックスが使用されるが、弱点として先頭のキーが外れるとインデックスが使われなくなる。その場合に複数列インデックスには、bloomインデックスを使用することで、先頭のキーが条件が外れていてもインデックスが使われる。
bloomインデックス

実行計画の取得

SQLの前に「EXPLAIN」か「EXPLAIN ANALYZE」を付ける。
EXPLAIN は、実行計画を取得できます。
「ANALYZE」オプションを使用することで、予想コストに加え、実際にかかった処理時間等の情報も確認できます。
(※ ANALYZEオプションを付与すると、実際にクエリが実行されてしまうため、INSERT文や、DELETE文などを行う際は注意が必要です。)

主な演算子一覧
分類演算子処理
テーブルスキャンSeq scanインデックスを使用せず、全件を検索
Index scanインデックスを使用してスキャン
Bitmap scanビットマップを使用してスキャン
Index only scan問い合わせがインデックスに含まれるカラムのみで完結する場合のスキャン
Tid scan検索条件がタプルID(ctid)のスキャン
その他のスキャンFunction scan関数がデータをgatherした結果をスキャン
テーブルの結合Nested Loopネステッド・ループ結合を行う
Merge Joinソート・マージ結合を行う
Hash Joinハッシュ結合を行う

照合順序

照合順序については下記サイトを参照

 

運用としては、全て「C」で統一していく。
lc_collateとlc_ctypeは、デフォルトで「C」、lc_messages, lc_monetary, lc_numeric, lc_timeに関してはpostgresql.confファイル内で指定することができる。変更したらPostgresサービスを再起動して反映させる。
※弊害としては、pg_logに出力されるログのメッセージは日本語ではなく英語になる。

lc_collateC
lc_ctypeC
lc_messagesC
lc_monetaryC
lc_numericC
lc_timeC
現在設定確認SQL
SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%'
照合順序確認SQL
WITH a AS (
	SELECT * FROM generate_series(ascii('A'), ascii('Z')) ascii
	UNION ALL SELECT * FROM generate_series(ascii('A'), ascii('Z'))
	UNION ALL SELECT * FROM generate_series(ascii('a'), ascii('z'))
), b AS (
	SELECT chr(ascii) FROM a
)
SELECT array_agg(chr ORDER BY chr) FROM b ;
照合順序「C」(文字コード順)
{A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z}
 
文字コード順
は(清音)
ば(濁音)
ぱ(半濁音)
ハ(清音)
バ(濁音)
パ(半濁音)
照合順序「Japanese_Japan.932」
{a,A,A,b,B,B,c,C,C,d,D,D,e,E,E,f,F,F,g,G,G,h,H,H,i,I,I,j,J,J,k,K,K,l,L,L,m,M,M,n,N,N,o,O,O,p,P,P,q,Q,Q,r,R,R,s,S,S,t,T,T,u,U,U,v,V,V,w,W,W,x,X,X,y,Y,Y,z,Z,Z}
 
アイウエオ一つずつ「カタカナ → ひらがな」の順に並びます。
ハ(清音)
は(清音)
バ(濁音)
ば(濁音)
パ(半濁音)
ぱ(半濁音)

他端末から接続できない場合

エラー「no pg_hba.conf entry for host <IPアドレス> … SSL off」などが出た場合

  • Windowsファイアウォールで、Postgresのポート5432を許可するようにする。
  • dataフォルダ(PostgreSQLフォルダ配下に無い場合、データ専用のドライブ側にある)の pg_hda.conf ファイルを修正する。

他ホストから接続するための設定変更

バッチからCOPYの絶対パスを渡す

COPYのFROM/TOは、絶対パスのみで相対パスは「relative path not allowed for COPY to file」エラーになる。
バインド変数として絶対パスを渡す。

Test.bat
REM PostgreSQLのインストールパスのbinディレクトリ
set PGPATH=D:\Program Files\PostgreSQL\9.6\bin\
 
psql -f "Test.sql" -v id='1234' -v inputpath="'E:/Work/Test/tmp/sample_dump'"
Test.sql
SET CLIENT_ENCODING TO 'UTF8';
COPY (SELECT * FROM fuga WHERE id = :id) TO :inputpath;

パフォーマンス向上

その他

  • postgresql.confのlc_messagesを「Japanese_Japan.932」から「C」にすることでエラーメッセージが文字化けしなくなる。
  • PostgreSQLの場合、テーブルのカラム名に予約語(例 “DO”)があるとそのままでは使えない。二重引用符“do”を付与すれば使える。
  • PostgreSQLのCSVインポートする場合、COPY コマンドで取り込む UTF8 のテキストファイルは BOM 無しにする。
  • 名前付きパラメータクエリーは、OLEDB版は使えた(プレースホルダ :xxxx)がODBC版では使えない。パラメータクエリーのプレースホルダは“?”である。
  • DECODE文は無いため、CASE文に変更する必要がある。
  • FROM句 or WHERE句内で使用する場合、PostgreSQLではエイリアスが必要となる。
  • PostgreSQLの場合、SELECTで別名にしたカラム名をORDER BY句で指定できないため、一段上にSELECTを追加する必要があった。
  • PostgreSQLの場合、1桁のvarchar型にシングルクォーテーションを付けないで指定するとエラーになる。修正例 AA=1 → AA='1'
  • PostgreSQLの場合、Numeric型にシングルクォーテーションを付けて指定するとエラーになる。修正例 AA='1' → AA=1
  • PostgreSQLの場合、MAX値取得でORDER BY句があるとエラーになる。そもそもORDER BY句が不要。
  • FORMAT関数ではパーセント(%)をエスケープで2つ(%%)にする必要がある。
  • PostgreSQLの場合、日付型を文字型にした場合に、例「ERROR: 日付が範囲外です: “20150406”」となる。
    修正例 TO_DATE(MAX(DE),'YYYY/MM/DD') AS DE FROM TEST → TO_CHAR(TO_DATE(MAX(DE),'YYYYMMDD'), 'YYYY/MM/DD') AS DE FROM TEST
  • FROMとテーブル名の区切りが全角空白だとエラーになるので半角空白にする。

エラーコード一覧

パスワードは英大小文字を区別する。
例 28P01:invalid_password

PostgreSQLエラーコード

参照

it技術/データベース/postgresql.txt · 最終更新: 2024/02/23 21:20 by yajuadmin