Explainとは

Explainは推定された実行計画を表示するコマンドです。

『実行計画＝最適なのか』を確認する際に使用します。

-- 実行方法：select文の前に「explain」を加えるだけでOK
explain select * from XXXXX;

ExplainとExplain Analyze

「Explain」には、「Explain」と「Explain Analyze」という二つの書き方があります。

Explain AnalyzeもExplainと同様、SELECT文の先頭に付けて実行します。

二つの違いは、

Explain　            　…　推定された実行計画を表示する

Explain Analyze　…　推定ではなく、一度実行してみた結果を表示する

--推定された実行計画を表示する
explain select * from XXXXX;         

--推定ではなく、一度実行した結果を表示する
explain analyze select * from XXXXX; 

costの見方

実際にexplainを実行すると、「cost」という値が返ってきます。

cost=A..B
-- A ＝ 初期コスト（select文に対して最初の行を返すコスト）
-- B ＝ トータルコスト（最後の行を返すコスト）

重要なのはトータルコストのほうです。また、costの値は相対値であり、単位はありません。

（シーケンシャルで1ページを読み込む場合のコストを1.0として示されます）

cost以外の値（rows、width）

cost以外の値、rows、widthは次のとおり。（widthはそれほど重要ではない）

cost=A..B rows=1000 width=4
-- rows = select文の結果として推定された行数を表示（あってない場合も全然ある）
-- width = 取得される行の平均サイズ(integer:4 , boolean:1 ・・・）

rowsの値が実際とかけ離れている場合、vacuum、analyzeし統計情報を更新を検討します。

vacuum、analyzeの説明はこちらに記載しています。

actual time

explain analyzeを実行した時には、実際の実行時間が返ってきます。

actual time=A..B rows=800 loops=1
-- time = 実際の実行時間（ミリ秒）
-- loops = 処理の繰り返し回数

○○ Scanの違い

返ってきた結果で○○ Scanと書いてあるところの説明です。

・Seq Scan                  …　全行スキャンしている（インデックスを使用していない）

・Index Scan　          …　インデックスを使用している

・Bitmap Scan　       …　（例えば複数の）インデックスを効率的に使用してスキャンしている

・Index Only Scan　…　テーブルのアクセスを省略して検索。非常に高速

※ Index Only Scanは、テーブルに直接アクセスせずVisibility Mapから取得する

Sort

Sort Methodの説明です。

・quick sort Memory   …　メモリ上のみで処理ができるため早い

・external sort dsik　  …　メモリ＋ディスクへのアクセスも発生するため時間がかかる

Nested loop , Merge Join , Hash Joinについて

テーブル同士の結合について書いています。

・Nested loop …　全行×全行のイメージ

・Merge Join …　結合するテーブルをそれぞれソートした後に結合する。走査回数が減る

・Hash Join …　あらかじめテーブルのハッシュ表を作り、結合するテーブルと突き合わせる

※ Hash Joinのハッシュ表はメモリに収めておく。

比較すると

・Nested loop …　データが小さい場合に有効

・Merge Join  …　データが多い場合に有効

・Hash Join    …　メモリーが十分にある場合に有効

参考・引用

次の資料を参考にさせていただきました。

『Slide Share , https://www.slideshare.net/MikiShimogai/sql-42453213,SQLチューニング入門 入門編,

PostgreSQLカンファレンス2014 2014年12月5日, TIS株式会社下雅意美紀』

PostgreSQLのバージョンを確認する

PostgreSQLのバージョンを確認する簡単な方法を紹介します。

① psqlで確認する

② select文で確認する

③ pgAdminで確認する

psqlで確認する

$ psql --version
psql (PostgreSQL) 13.2

Windowsでの実行確認

Linux（Ubuntu Server）での実行確認

select文で確認する

下記のSQLを実行すると確認できます。

--バージョンを取得するSQL
select * From version();

実行した結果です。

上のSQLはpgAdmin上で実行しても確認することができます。

pgAdminから確認する方法

① pgAdminを開きデータベースを選択します。

② プロパティタブを開きます

③ バージョンの個所に表示されます。（画像の場合はバージョン12.3です）

四捨五入

四捨五入にはroundを使います。

他の言語のように、カンマで有効桁数を指定する方式ではないので注意です。

val number:Double = 1.23456
println(Math.round(number))                     // 小数第1位四捨五入 : 1
println(Math.round(number * 10.0) / 10.0)       // 小数第2位四捨五入 : 1.2
println(Math.round(number * 100.0) / 100.0)     // 小数第3位四捨五入 : 1.23
println(Math.round(number * 1000.0) / 1000.0)   // 小数第4位四捨五入 : 1.235

切り上げ

切り上げにはceilを使います。

val number:Double = 1.23456
println(Math.ceil(number))                     // 小数第1位四捨五入 : 2.0
println(Math.ceil(number * 10.0) / 10.0)       // 小数第2位四捨五入 : 1.3
println(Math.ceil(number * 100.0) / 100.0)     // 小数第3位四捨五入 : 1.24
println(Math.ceil(number * 1000.0) / 1000.0)   // 小数第4位四捨五入 : 1.235

切り捨て

切り上げにはfloorを使います。

val number:Double = 1.23456
println(Math.floor(number))                   // 小数第1位四捨五入 : 1.0
println(Math.floor(number * 10.0) / 10.0)     // 小数第2位四捨五入 : 1.2
println(Math.floor(number * 100.0) / 100.0)   // 小数第3位四捨五入 : 1.23
println(Math.floor(number * 1000.0) / 1000.0) // 小数第4位四捨五入 : 1.234

データを登録する2つの方法

PostgreSQLでのテーブルにデータを新規登録・更新するには、以下の方法が主にあります。

１．pgAdminで手入力でデータを新規登録・更新する

２．INSERT文を使用してデータを新規登録する方法

３．UPDATE文を使用してデータを更新する方法

１の場合、画面で入力するため入力が簡単な反面、大量データの登録には向きません。

２と3の場合、構文を覚える必要がありますが、1と比べ大量・高速にデータの登録ができます。

ここではそれぞれの方法について紹介しますので、扱いやすいほうを使用してください。

１．pgAdminで手入力でデータを登録する

pgAdminでのデータの登録方法について手順を説明します。

1-１．①テーブルを右クリック、②「データの閲覧/編集」から「すべての行」を選択します。

すでに大量のデータが登録されている場合、「最初の100行」でも構いません。

1-２．データが表示される画面が開くので、編集したい行をクリックします。

編集ができない場合はこちらを参照してみて下さい。

> データが編集できない時の原因と対処方法

1-３．データを入力していきます。（Tabキーで次の列に移動できます）

1-４．入力が終わったら、最後に画面上にある、「変更データを保存」

もしくは、「F6]キーを押すと保存ができます。

1-５．問題なく保存できたらメッセージが表示されます。

２－１．INSERT文を使用してデータを登録する方法

INSERT文とはテーブルにデータを新規登録するSQLです。

まず、代表的なINSERT文の構文と例を紹介します。

・ 基本的なINSERT文の形

INSERT文は基本的には次のような形になります。

-- 基本的なINSERT文の形
INSERT INTO テーブル名(列名1 , 列名2, ・・・) VALUES (列名1の値 , 列名2の値, ・・・);

-- 例
insert into department(department_code,department_name)values('a','営業部');

・ 列を書く順番は自由でOK

列を書く順番は自由でOKです。（テーブルと同じでなくても構いません。）

※ ただし見易さの関係で主キーの列が先頭に来るケースがほとんどだと思います。

-- 列の順番は自由でOK
INSERT INTO テーブル名(列名3, 列名1, 列名2,・・・) VALUES (列名3の値 , 列名1の値, 列名2の値,・・・);

-- 例
insert into department(department_name,department_code)values('総務部','b');

・ 列名は省略できる

テーブル名の次に書く列名は省略でき、いきなりVALUESで書けます。

しかしその時のVALUESに書く値の順番は、

その場合テーブルの先頭にある列から最後の列までになるので気を付けて下さい。

-- 列名を省略した形
INSERT INTO テーブル名 VALUES (列名1の値 , 列名2の値, ・・・, 最終列の値);

-- 例
insert into department values('c','製造部');

・ 複数一度に実行できる

1行のINSERTの最後に「;」を入れれば、複数行一度に実行できます。

--「;」でINSERT文は複数つなげられる
INSERT INTO テーブル名(列名3, 列名1, 列名2,・・・) VALUES (列名3の値 , 列名1の値, 列名2の値,・・・);
INSERT INTO テーブル名(列名3, 列名1, 列名2,・・・) VALUES (列名3の値 , 列名1の値, 列名2の値,・・・);
INSERT INTO テーブル名(列名3, 列名1, 列名2,・・・) VALUES (列名3の値 , 列名1の値, 列名2の値,・・・);

--例（次の3行は一度に実行可能）
insert into department(department_code,department_name) values('d','経理部');
insert into department(department_code,department_name) values('e','人事部');
insert into department(department_code,department_name) values('f','物流部');

２－２．INSERT文の実行方法

上で紹介した例文は、pgAdminのクエリツールより実行ができます。

１． pgAdminを開きクエリツールを起動します。

pgAdminを開き、次のように①データベースを選択した状態で、②クエリツールを開きます。

２． pgAdminを開きクエリツールを起動します。

①の箇所にINSERT文を記述し、②実行ボタンを押します。

３． クエリ実行成功のメッセージが表示されることを確認

成功のメッセージが表示されれば無事に登録ができました。

３．UPDATE文を使用してデータを登録する方法

UPDATE文とはテーブルのデータを更新するSQLです。

・ 基本的なUPDATE文の形

UPDATE文は基本的には次のような形になります。

-- 基本的なUPDATE文の形
UPDATE テーブル名 SET 列名1 = 値1 , 列名2 = 値2 WHERE 条件文;

-- 例
update department set department_name = 'hoge' where department_code = 'a';

※ 実行の手順はINSERT文の方法と同じです。

以上が登録（新規登録・更新）までの手順になります。

登録したデータを見るにはテーブルのデータを取得するを参照してください。

小文字を大文字に変換する

小文字を大文字に変換するには、upperを使用します。

-- 小文字を大文字に変換する
upper( 変換したい文字列 )

実行例

upperの使用例です。※下の例はそのまま実行できます

-- 使用例 
select upper('abc');      --"ABC"
select upper('aBc');      --"ABC"
select upper('ａｂｃ');   --"ＡＢＣ"

注意点

全角小文字が大文字にならない時、ロケールの設定が「C」になっている可能性があります。（ロケールが「C」の時は、全角小文字がupperされません。）

その場合は、translateを使用して1文字ずつ変換すれば解決できます。

-- ロケールが「C」の時は、全角小文字がupperされない
select upper('ａｂｃ');   --"ａｂｃ"

-- translateを使用することで変換ができるようになります。
select translate('ａｂｃ', 'ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ', 'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ');   --"ＡＢＣ"