pg执行计划分析小笔记

转载

mb5fe18f5282239 2019-10-09 11:42:00

开发同事问，为什么一个标量子查询，放在where子句后进行大小判断，比不放在where子句后进行判断大小运行的更快？按道理加了一次判断，不是应该变慢么？

把语句拿过来，看了一下两个语句的执行计划：

语句1和执行计划1：

SELECT A.*,
       /*剩余量*/
       (A.q_basic -
       (SELECT COALESCE(SUM(d.q_basic), 0.00)
           FROM e_order d
          WHERE CAST(d.r_item AS INT) = A.ID
            AND d.req_status NOT IN ('FZ'))) AS surplus
  FROM e_order A
  LEFT JOIN e_requirement b
    ON A.requirement_no = b.requirement_no
  LEFT JOIN erp_project C
    ON b.factory = C.project_no
 WHERE 1 = 1
   AND A.status IN ('WC')
   AND (A.q_basic -
       (SELECT COALESCE(SUM(d.q_basic), 0.00)
           FROM e_order d
          WHERE CAST(d.r_item AS INT) = A.ID
            AND d.req_status NOT IN ('FZ'))) > 0.00
   AND (C.project_name LIKE CONCAT('%', 'csg18098', '%') OR
       C.project_no LIKE CONCAT('%', 'csg18098', '%'))
   AND A.requirement_no LIKE CONCAT('%', '0000004390', '%');

pg执行计划分析小笔记_执行计划

语句2和执行计划2：

SELECT A.*,
       /*剩余量*/
       (A.q_basic -
       (SELECT COALESCE(SUM(d.q_basic), 0.00)
           FROM e_order d
          WHERE CAST(d.r_item AS INT) = A.ID
            AND d.req_status NOT IN ('FZ'))) AS surplus
  FROM e_order A
  LEFT JOIN e_requirement b
    ON A.requirement_no = b.requirement_no
  LEFT JOIN erp_project C
    ON b.factory = C.project_no
 WHERE 1 = 1
   AND A.status IN ('WC')
   AND (C.project_name LIKE CONCAT('%', 'csg18098', '%') OR
       C.project_no LIKE CONCAT('%', 'csg18098', '%'))
   AND A.requirement_no LIKE CONCAT('%', '0000004390', '%')

pg执行计划分析小笔记_执行计划_02

从上面的执行计划看，在where之后进行大小判断后，执行时间是662.954 ms；去掉判断后执行时间是1549.644 ms。的确如开发所说。

现在分别来看上面的两个执行计划。

语句1在where子句后增加判断，表关联的顺序是((((a,d_1),b),c),d)。语句2不在where子句后加判断的关联顺序是(((a,b),c),d)。

其实这里d_1就是表示在where子句后的表e_order。这一点，可以将语句修改一下，就可以得到验证：

SELECT A.*,
       /*剩余量*/
       (A.q_basic -
       (SELECT COALESCE(SUM(d.q_basic), 0.00)
           FROM e_order d
          WHERE CAST(d.r_item AS INT) = A.ID
            AND d.req_status NOT IN ('FZ'))) AS surplus
  FROM e_order A
  LEFT JOIN e_requirement b
    ON A.requirement_no = b.requirement_no
  LEFT JOIN erp_project C
    ON b.factory = C.project_no
 WHERE 1 = 1
   AND A.status IN ('WC')
   AND (A.q_basic -
       (SELECT COALESCE(SUM(e.q_basic), 0.00)
           FROM e_order e
          WHERE CAST(e.r_item AS INT) = A.ID
            AND e.req_status NOT IN ('FZ'))) > 0.00
   AND (C.project_name LIKE CONCAT('%', 'csg18098', '%') OR
       C.project_no LIKE CONCAT('%', 'csg18098', '%'))
   AND A.requirement_no LIKE CONCAT('%', '0000004390', '%');