はじめに

クエリの実行時間がシビアなプロジェクトでは、
EXPLAINの結果と睨めっこして、SQLのパフォーマンスチューニングを行うことがあるのですが、
通常のEXPLAIN (ANALYZE) ってこんな感じ

       QUERY PLAN                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=268.24..276.35 rows=1 width=8) (actual time=35.423..35.611 rows=2 loops=1)
   Hash Cond: (((c.area_code)::text = (p.area_code)::text) AND ((c.area_detail_code)::text = (p.area_detail_code)::text))
   ->  Hash Semi Join  (cost=179.01..187.11 rows=2 width=24) (actual time=29.091..29.254 rows=2 loops=1)
         Hash Cond: (((c.area_code)::text = (w.area_code)::text) AND ((c.area_detail_code)::text = (w.area_detail_code)::text))
         ->  Nested Loop  (cost=112.70..120.74 rows=6 width=16) (actual time=0.532..0.715 rows=4 loops=1)
               ->  HashAggregate  (cost=112.42..112.43 rows=1 width=8) (actual time=0.483..0.515 rows=4 loops=1)
                     Group Key: (a.area_code)::text, (a.area_detail_code)::text
                     ->  Seq Scan on address a  (cost=0.00..112.41 rows=1 width=8) (actual time=0.162..0.443 rows=4 loops=1)
                           Filter: ((address_name)::text ~~ '岐阜%--'::text) 
                           Rows Removed by Filter: 5549
               ->  Index Only Scan using area_code_area_detail_code_code_idx on code c  (cost=0.28..8.30 rows=1 width=8) (actual time=0.014..0.022 rows=1 loops=4)
                     Index Cond: ((area_code = (a.area_code)::text) AND (area_detail_code = (a.area_detail_code)::text))
                     Heap Fetches: 4
         ->  Hash  (cost=37.74..37.74 rows=1905 width=8) (actual time=28.471..28.477 rows=1903 loops=1)
               Buckets: 2048  Batches: 1  Memory Usage: 91kB
               ->  Seq Scan on work w  (cost=0.00..37.74 rows=1905 width=8) (actual time=0.016..14.279 rows=1903 loops=1)
                     Filter: (worker_ratio > '50'::numeric)
                     Rows Removed by Filter: 76
   ->  Hash  (cost=84.15..84.15 rows=339 width=8) (actual time=6.297..6.303 rows=337 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 22kB
         ->  Bitmap Heap Scan on population p  (cost=10.91..84.15 rows=339 width=8) (actual time=0.065..3.228 rows=337 loops=1)
               Recheck Cond: (pop_total > 300000)
               Heap Blocks: exact=52
               ->  Bitmap Index Scan on pop_total_population_idx  (cost=0.00..10.83 rows=339 width=0) (actual time=0.036..0.042 rows=337 loops=1)
                     Index Cond: (pop_total > 300000)
 Planning time: 1.856 ms
 Execution time: 35.916 ms
(27 rows)

で、結構みづらいんですよね。（もっと機械的に見える化できるはずだ、という意味で）

で、なんかいいツールはないかなと思っていたらこんなツールが
Postgres EXPLAIN Visualizer (pev)

ありました。

f:id:hiroyannnn:20190804151331p:plain — EXPLAINビジュアライザ

事前準備

postgres の準備

docker のバージョン

$ docker -v
Docker version 18.09.2, build 6247962
$ docker-compose -v
docker-compose version 1.23.2, build 1110ad01

Dockerfileをクローン

$ git clone https://github.com/docker-library/postgres.git

docker-compose.ymlを作成する

db-postgres:
  build: ./postgres/10/alpine/
  ports:
   - "5432:5432"
  environment:
   - POSTGRES_USER=postgres
   - POSTGRES_PASSWORD=mysecretpassword1234
   - PGPASSWORD=mysecretpassword1234
   - POSTGRES_DB=testdb
   - DATABASE_HOST=localhost
  volumes:
   - ./data:/data

dockerのビルド＆起動

$ docker-compose up

postgresに接続（下記はdockerコンテナ内から接続）

bash-5.0# psql -h localhost -U postgres
psql (10.9)
Type "help" for help.

postgres=#

データの準備

データはなんでもいいですがこの辺使っていきます。

データの加工とinsert

CSVのままだと使えないので、以下を編集
1. ヘッダまでを削除（データのみにする）
2. INT部分のNULLに「-」を使用しているので、-を削除
3. 文字コードをSJISからUTF-8に変更
とか諸々して、適当に正規化してテーブル作成、インポート

INDEXも適当に貼っておきます。

postgres=# 
CREATE TABLE population
(
  populationId INT NOT NULL PRIMARY KEY, 
  category varchar(50),
  area_code varchar(50),
  area_detail_code varchar(50),
  pop_total INT,
  pop_m INT,
  pop_f INT,
  pop_r DECIMAL,
  house_holds_total INT,
  house_holds INT,
  col2 INT,
  col3 INT,
  col4 INT,
  col5 INT
);

COPY population FROM '/data/pop.csv' with CSV;

CREATE TABLE address
(
  area_code varchar(50),
  area_detail_code varchar(50),
  address_name varchar(50)
);

COPY address FROM '/data/address.csv' with CSV;

CREATE TABLE code
(
  area_code varchar(50),
  area_detail_code varchar(50)
);
COPY code FROM '/data/code.csv' with CSV;

CREATE TABLE work
(
  workId INT NOT NULL PRIMARY KEY, -- primary key column
  area_code varchar(50),
  area_detail_code varchar(50),
  worker_ratio DECIMAL
);
COPY work FROM '/data/work.csv' with CSV;

CREATE INDEX area_code_code_idx ON code (area_code);
CREATE INDEX area_detail_code_code_idx ON code (area_detail_code);
CREATE INDEX area_code_area_detail_code_code_idx ON code (area_code,area_detail_code);
CREATE INDEX pop_total_population_idx ON population (pop_total);
CREATE INDEX worker_ratio_work_idx ON work (worker_ratio);

CREATE INDEX address_name_address_idx ON address (address_name);

クエリ

例えば、取得したいのは area_code で、
条件指定で、

人数が30万人以上
かつ、労働人口の比率が50%超
かつ、住所に「岐阜」が先頭に入る
のようなものを考えたときに、以下のようなクエリとなるとします。

SELECT * FROM code c
WHERE EXISTS (
  SELECT 1 FROM population p 
  WHERE pop_total > 300000
  AND p.area_code = c.area_code
  AND p.area_detail_code = c.area_detail_code
) AND EXISTS (
  SELECT 1 FROM work w 
  WHERE worker_ratio > 50
  AND w.area_code = c.area_code
  AND w.area_detail_code = c.area_detail_code
) AND EXISTS (
  SELECT 1 FROM address a 
  WHERE address_name like '岐阜%'
  AND a.area_code = c.area_code
  AND a.area_detail_code = c.area_detail_code
);

ちなみに得られる結果は以下。

 area_code | area_detail_code 
-----------+------------------
 21000     | a
 21201     | 2
(2 rows)

上記をEXPLAINし、何がボトルネックになっているかをみていくわけです。

通常のexplain結果でも慣れてれば、読めるんですけど
見辛いんですよね。

そこで前述のビジュアライザで可視化すると、
結構楽しくチューニングができます。

以下のオプションでEXPLAIN

EXPLAIN (ANALYZE ,FORMAT JSON) -- jsonで出力する
SELECT * FROM code c
WHERE EXISTS (
  SELECT 1 FROM population p 
  WHERE pop_total > 300000
  AND p.area_code = c.area_code
  AND p.area_detail_code = c.area_detail_code
) AND EXISTS (
  SELECT 1 FROM work w 
  WHERE worker_ratio > 50
  AND w.area_code = c.area_code
  AND w.area_detail_code = c.area_detail_code
) AND EXISTS (
  SELECT 1 FROM address a 
  WHERE address_name like '岐阜%'
  AND a.area_code = c.area_code
  AND a.area_detail_code = c.area_detail_code
);

bashからpsqlとか使う場合は
以下だとやりやすいかと

bash-5.0# psql -U postgres -h localhost -A -t -c "
EXPLAIN (ANALYZE ,FORMAT JSON)
SELECT * FROM code c
WHERE EXISTS (
  SELECT 1 FROM population p 
  WHERE pop_total > 300000
  AND p.area_code = c.area_code
  AND p.area_detail_code = c.area_detail_code
) AND EXISTS (
  SELECT 1 FROM work w 
  WHERE worker_ratio > 50
  AND w.area_code = c.area_code
  AND w.area_detail_code = c.area_detail_code
) AND EXISTS (
  SELECT 1 FROM address a 
  WHERE address_name like '岐阜%'
  AND a.area_code = c.area_code
  AND a.area_detail_code = c.area_detail_code
);" > /data/out

出力されたJSONを貼り付け f:id:hiroyannnn:20190804152920p:plain

クエリのタイトルや、実際のクエリを貼り付けてもいいです。（貼り付けなくてもいいです。）

で、SUBMITを押下
画面左側の歯車マークから

graph metric:  
rows  (またはduration)

あたりを選択しておくと、わかりやすくなるかと思います。 f:id:hiroyannnn:20190804153145p:plain

durationやrowsで赤くなってるところを
条件変えるとか、index見直すとかしてチューニングすればいいんですね〜

hiroyannnnの雑記

エンジニアの雑記です。

postgreSQLでのEXPLAINをわかりやすくする

はじめに

事前準備

postgres の準備

docker のバージョン

Dockerfileをクローン

docker-compose.ymlを作成する

dockerのビルド＆起動

postgresに接続（下記はdockerコンテナ内から接続）

データの準備

データの加工とinsert

クエリ

はじめに

事前準備

postgres の準備

docker のバージョン

Dockerfileをクローン

docker-compose.ymlを作成する

dockerのビルド＆起動

postgresに接続（下記はdockerコンテナ内から接続 ）

データの準備

データの加工とinsert

クエリ

postgresに接続（下記はdockerコンテナ内から接続）