执行计划分析 · datalake · 可视化

一、背景

 本文介绍通过工具以可视化的方式分析postgres或greenplum的执行计划。

二、Postgres示例

执行计划：

Nested Loop Left Join (cost=11.95..28.52 rows=5 width=157) (actual time=0.010..0.010 rows=0 loops=1)

Output: rel_users_exams.user_username, rel_users_exams.exam_id, rel_users_exams.started_at, rel_users_exams.finished_at, exam_1.id, exam_1.title, exam_1.date_from, exam_1.date_to, exam_1.created, exam_1.created_by_, exam_1.duration, exam_1.success_threshold, exam_1.published

Inner Unique: true

Join Filter: (exam_1.id = rel_users_exams.exam_id)

Buffers: shared hit=1

->  Bitmap Heap Scan on public.rel_users_exams  (cost=11.80..20.27 rows=5 width=52) (actual time=0.009..0.009 rows=0 loops=1)

Output: rel_users_exams.user_username, rel_users_exams.exam_id, rel_users_exams.started_at, rel_users_exams.finished_at

Recheck Cond: (1 = rel_users_exams.exam_id)

Buffers: shared hit=1

->  Bitmap Index Scan on rel_users_exams_pkey  (cost=0.00..11.80 rows=5 width=0) (actual time=0.005..0.005 rows=0 loops=1)

Index Cond: (1 = rel_users_exams.exam_id)

Buffers: shared hit=1

->  Materialize  (cost=0.15..8.17 rows=1 width=105) (never executed)

Output: exam_1.id, exam_1.title, exam_1.date_from, exam_1.date_to, exam_1.created, exam_1.created_by_, exam_1.duration, exam_1.success_threshold, exam_1.published

->  Index Scan using exam_pkey on public.exam exam_1  (cost=0.15..8.17 rows=1 width=105) (never executed)

Output: exam_1.id, exam_1.title, exam_1.date_from, exam_1.date_to, exam_1.created, exam_1.created_by_, exam_1.duration, exam_1.success_threshold, exam_1.published

Index Cond: (exam_1.id = 1)

Planning Time: 1.110 ms

Execution Time: 0.170 ms

图1 Postgres

三、Greenplum示例

执行计划：

Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..431.00 rows=1 width=8)

  ->  Sequence  (cost=0.00..431.00 rows=1 width=8)

        ->  Partition Selector for sales (dynamic scan id: 1)  (cost=10.00..100.00 rows=50 width=4)

              Filter: year = 2015

              Partitions selected:  1 (out of 100)

        ->  Dynamic Table Scan on sales (dynamic scan id: 1)  (cost=0.00..431.00 rows=1 width=8)

              Filter: year = 2015

Settings:  optimizer=on

Optimizer status: PQO version 1.620

(9 rows)

图2 Greenplum

三、项目代码

https://github.com/sinwaj/pev2