最近项目上优化查询性能,了解下 explain,pg使用版本10.1

语法

EXPLAIN [ ( option [, ...] ) ] statement

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

ANALYZE [ boolean ]

VERBOSE [ boolean ]

COSTS [ boolean ]

BUFFERS [ boolean ]

TIMING [ boolean ]

FORMAT { TEXT | XML | JSON | YAML }

各参数含义

ANALYZE

执行语句并显示真正的运行时间和其它统计信息,默认值False ,

注意:ANALYZE会真正执行SQL语句

VERBOSE

显示额外的信息,尤其是计划树中每个节点的字段列表,schema识别表和函数名称。总是打印统计数据中显示的每个触发器的名字。这个参数缺省为FALSE。

COSTS

包括每个计划节点的启动成本预估和总成本的消耗，也包括行数和行宽度的预估。这个参数缺省为TRUE。

BUFFERS

使用信息。特别包括共享块命中、读、脏和写的次数，本地块命中、读、脏和写，临时块读和写的次数。

命中意味着避免了物理读，因为块在需要时已经在缓存中发现了。共享块包含普通表和索引的数据；

本地块包含临时表和索引的数据；而临时块包含用于排序、哈希、物化计划节点和类似情况的短期工作数据。

脏块的数量表示该查询之前改变且未提交的块的数量;写块的数量表示在查询时被后台进程从缓存释放的脏块数量。

上层节点显示的块的数量是所有它的子节点使用块的数量合计。在文本格式中只打印非零值。

该参数可能只在ANALYZE也启用的时候使用。它的缺省为FALSE。

TIMING

在输出中包含实际启动时间和每个节点花费的时间。重复读系统块在某些系统上会显著的减缓查询的速度，

所以当需要只统计实际行数且没有准确时间时，该参数设置为FALSE会很有用。即使节点级别时间统计被关闭，整个语句的运行时间也是要被计量的。

这个参数可能只在ANALYZE也启用的时候使用。缺省为TRUE。

FORMAT

声明输出格式，可以为TEXT, XML, JSON 或 YAML。非文本的输出包含文本输出格式相同的信息，但是更容易被程序解析。这个参数缺省为TEXT。

实战

EXPLAIN (analyze,buffers,verbose)

select

max(employee.id),

max(user_profile.name),

max(employee.login_name),

max(employee.status),

max(employee.create_at),

max(user_profile.wx_id),

array_to_string(group_concat(role.name),',')

from employee

left join user_profileon employee.user_id = user_profile.id

left join employee_role_permiton employee_role_permit.employee_id = employee.id

left join role on role.id = employee_role_permit.role_id

where employee.sys_id =10000000830

and (employee.sys_type_id =14)

and (role.sp_type<>1 or role.sp_type is null)

-- GROUP BY employee.id;

解析:

Aggregate (cost=95.40..95.41 rows=1 width=72) (actual time=1.083..1.083 rows=1 loops=1)

Output: max(employee.id), max(employee.id), max((user_profile.name)::text), max((employee.login_name)::text), max(employee.status), max(employee.create_at), max((user_profile.wx_id)::text), array_to_string(group_concat(role.name), ','::text)

Buffers: shared hit=48

-> Nested Loop Left Join (cost=11.82..95.38 rows=1 width=72) (actual time=0.087..1.058 rows=2 loops=1)

Output: employee.id, employee.login_name, employee.status, employee.create_at, user_profile.name, user_profile.wx_id, role.name

Filter: ((role.sp_type <> 1) OR (role.sp_type IS NULL))

Rows Removed by Filter: 1

Buffers: shared hit=48

-> Nested Loop Left Join (cost=11.68..95.21 rows=1 width=64) (actual time=0.083..1.048 rows=3 loops=1)

Output: employee.id, employee.login_name, employee.status, employee.create_at, user_profile.name, user_profile.wx_id, employee_role_permit.role_id

Buffers: shared hit=42

-> Hash Right Join (cost=11.40..86.90 rows=1 width=48) (actual time=0.074..1.030 rows=3 loops=1)

Output: employee.id, employee.login_name, employee.status, employee.create_at, employee.user_id, employee_role_permit.role_id

Hash Cond: (employee_role_permit.employee_id = employee.id)

Buffers: shared hit=33

-> Seq Scan on public.employee_role_permit (cost=0.00..62.81 rows=3381 width=16) (actual time=0.006..0.579 rows=3428 loops=1)

Output: employee_role_permit.employee_id, employee_role_permit.role_id

Buffers: shared hit=29

-> Hash (cost=11.39..11.39 rows=1 width=40) (actual time=0.021..0.021 rows=2 loops=1)

Output: employee.id, employee.login_name, employee.status, employee.create_at, employee.user_id

Buckets: 1024 Batches: 1 Memory Usage: 9kB

Buffers: shared hit=4

-> Index Scan using employee_sys_id_index on public.employee (cost=0.28..11.39 rows=1 width=40) (actual time=0.016..0.019 rows=2 loops=1)

Output: employee.id, employee.login_name, employee.status, employee.create_at, employee.user_id

Index Cond: (employee.sys_id = '10000000830'::bigint)

Filter: (employee.sys_type_id = 14)

Buffers: shared hit=4

-> Index Scan using user_profile_user_id_index on public.user_profile (cost=0.28..8.30 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=3)

Output: user_profile.id, user_profile.name, user_profile.city, user_profile.sex, user_profile.status, user_profile.wx_id, user_profile.wx_open_id, user_profile.email, user_profile.phone1, user_profile.phone2, user_profile.creator_id, user_profile.create_at, user_profile.update_at, user_profile.last_login

Index Cond: (employee.user_id = user_profile.id)

Buffers: shared hit=9

-> Index Scan using role_id_index on public.role (cost=0.14..0.16 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=3)

Output: role.name, role.id, role.sp_type

Index Cond: (role.id = employee_role_permit.role_id)

Buffers: shared hit=6

Planning time: 0.411 ms

Execution time: 1.176 ms

```

postgresql 优化查询之 EXPLAIN