PostgreSQL EXPLAIN の読み方

PostgreSQLのEXPLAINの読み方がまったくわからないのでかなり勉強になった

業務ですぐ活用できるかというのはわからない

DDLはそのままで下記のSQLはどうなるか

code:posts.sql

EXPLAIN ANALYZE SELECT COUNT(*) FROM posts WHERE user_id = 123;

-- query plan

Finalize Aggregate (cost=13914.37..13914.38 rows=1 width=8) (actual time=18.690..21.325 rows=1 loops=1)

-> Gather (cost=13914.15..13914.36 rows=2 width=8) (actual time=18.612..21.319 rows=3 loops=1)

Workers Planned: 2

Workers Launched: 2

-> Partial Aggregate (cost=12914.15..12914.16 rows=1 width=8) (actual time=15.942..15.943 rows=1 loops=3)

-> Parallel Seq Scan on posts (cost=0.00..12914.14 rows=5 width=0) (actual time=5.595..15.936 rows=4 loops=3)

Filter: (user_id = 123)

Rows Removed by Filter: 166661

Planning Time: 0.111 ms

Execution Time: 21.373 ms

(10 rows)

Parallel Seq Scan on posts とあって一番コストがかかっている箇所

INDEX を追加する

code:add_index.sql

CREATE INDEX userid_idx ON posts (user_id);

code:再度Explain.sql

EXPLAIN ANALYZE SELECT COUNT(*) FROM posts WHERE user_id = 123;

-- query plan

Aggregate (cost=4.64..4.65 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=1)

-> Index Only Scan using userid_idx on posts (cost=0.42..4.62 rows=11 width=0) (actual time=0.021..0.023 rows=12 loops=1)

Index Cond: (user_id = 123)

Heap Fetches: 0

Planning Time: 0.092 ms

Execution Time: 0.059 ms

(6 rows)

Index Only Scan using userid_idx on postsとあるように改善している

ただ実践ですぐできるかは怪しい

Analyze 結果の可視化は以下がよいようだ

https://gitlab.com/depesz/explain.depesz.com Docker で起動するという方法もありそう

pev2 を使ってみたがちょっと微妙

https://gyazo.com/be32b3c8aa6bf127ec3b3fc08db762da

depesz は適切に(？)ヒントをくれた

https://gyazo.com/f8df2657b093133b1b8c8e1e20f8f396

https://gyazo.com/174c26b355314a3b7f8bfa7dd8006309

さりとて外部サービスには投げたくないのでなぁ…