postgresql通过explain命令查看查询性能

explain(ANALYZE,VERBOSE,BUFFERS) select c.* from tb_classification c left join tb_operate_log o on c.id=o.object_id where 1=1 and c.parent_code = 'root000000' and o.operate_type >= 0 and o.id in (select max(so.id) from tb_operate_log so group by so.object_code) and o.object_type = 0 and o.version_id = '7edd57e0ebd843e38909aff16981e80d' order by o.id desc;

explain命令的输出可以看做是一个树形结构-查询计划树，树的每个节点包含对应的节点类型，作用对象以及其他属性如cost,rows,width等。

实际的查询是从下往上执行的，基于火山模型（每个节点返回一行记录给父节点）

• Planning time: 0.066 ms 表明了生成查询计划的时间
• Execution time: 0.160 ms 表明了实际的SQL 执行时间，其中不包括查询计划的生成时间

把条件带入子查询中，

explain(ANALYZE,VERBOSE,BUFFERS) select c.* from tb_classification c left join tb_operate_log o on c.id=o.object_id where 1=1 and c.parent_code = 'root000000' and o.operate_type >= 0 and o.id in (select max(so.id) from tb_operate_log so where so.object_type = 0 and so.version_id = '7edd57e0ebd843e38909aff16981e80d' group by so.object_code) order by o.id desc;