Search Postgresql Archives

execution plan is wrong, or the query ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Folks,

I have a following query that used to work as intended on 8.3.5 :

SELECT COUNT(*)
FROM jiveDeployRequest dr
LEFT JOIN jiveDeployType dt ON dr.deployTypeId = dt.deployTypeId
LEFT JOIN jiveDeployStatus ds ON dr.deployStatusId = ds.deployStatusId
LEFT OUTER JOIN jiveCustomerInstallationDeploy cid ON dr.deployRequestId = cid.deployRequestId
LEFT JOIN jiveCustomerInstallation ci ON cid.customerInstallationId = ci.customerInstallationId
LEFT JOIN jiveInstallationType it ON ci.installationTypeId=it.installationTypeId
LEFT OUTER JOIN jiveCloudUser cu ON dr.cloudUserId = cu.cloudUserId
WHERE cid.customerInstallationId = 660
  AND
    (SELECT CASE WHEN ds.statusCode <> 'inprocess'
     OR now()-lastStatusUpdate < interval '00:10:00' THEN statusCode ELSE 'unknown' END) = 'inprocess'
  AND dt.typeCode != 'disable-magic-admin'


The plan was as follows :

  1. Aggregate  (cost=1178.30..1178.31 rows=1 width=0)  
  2.   ->  Nested Loop  (cost=6.19..1178.29 rows=1 width=0)  
  3.         ->  Nested Loop Left Join  (cost=6.19..1178.01 rows=1 width=8)  
  4.               Join Filter: (ci.installationtypeid = it.installationtypeid)  
  5.               ->  Nested Loop Left Join  (cost=6.19..1176.61 rows=1 width=16)  
  6.                     ->  Nested Loop Left Join  (cost=6.19..1168.25 rows=1 width=16)  
  7.                           ->  Hash Left Join  (cost=6.19..1167.97 rows=1 width=24)  
  8.                                 Hash Cond: (dr.deploystatusid = ds.deploystatusid)  
  9.                                 Filter: (((subplan))::text = 'inprocess'::text)  
  10.                                 ->  Nested Loop  (cost=5.08..1163.12 rows=103 width=40)  
  11.                                       ->  Bitmap Heap Scan on jivecustomerinstallationdeploy cid  (cost=5.08..315.56 rows=103 width=16)  
  12.                                             Recheck Cond: (customerinstallationid = 660)  
  13.                                             ->  Bitmap Index Scan on jcid_customerinstallationid  (cost=0.00..5.05 rows=103 width=0)  
  14.                                                   Index Cond: (customerinstallationid = 660)  
  15.                                       ->  Index Scan using jivedeployrequest_pk on jivedeployrequest dr  (cost=0.00..8.22 rows=1 width=40)  
  16.                                             Index Cond: (dr.deployrequestid = cid.deployrequestid)  
  17.                                 ->  Hash  (cost=1.05..1.05 rows=5 width=16)  
  18.                                       ->  Seq Scan on jivedeploystatus ds  (cost=0.00..1.05 rows=5 width=16)  
  19.                                 SubPlan  
  20.                                   ->  Result  (cost=0.00..0.02 rows=1 width=0)  
  21.                           ->  Index Scan using jiveclouduser_pk on jiveclouduser cu  (cost=0.00..0.27 rows=1 width=8)  
  22.                                 Index Cond: (dr.clouduserid = cu.clouduserid)  
  23.                     ->  Index Scan using jivecustomerinstallation_pk on jivecustomerinstallation ci  (cost=0.00..8.34 rows=1 width=16)  
  24.                           Index Cond: ((ci.customerinstallationid = 660) AND (cid.customerinstallationid = ci.customerinstallationid))  
  25.               ->  Seq Scan on jiveinstallationtype it  (cost=0.00..1.18 rows=18 width=8)  
  26.         ->  Index Scan using jivedeploytype_pk on jivedeploytype dt  (cost=0.00..0.27 rows=1 width=8)  
  27.               Index Cond: (dt.deploytypeid = dr.deploytypeid)  
  28.               Filter: ((dt.typecode)::text <> 'disable-magic-admin'::text) 

After upgrade to 9.2 the query doesn't return the same results any more, and the execution plan has changed :


  1.  Aggregate  (cost=17.15..17.16 rows=1 width=0)  
  2.    ->  Nested Loop Left Join  (cost=0.00..17.15 rows=1 width=0)  
  3.          ->  Nested Loop  (cost=0.00..16.85 rows=1 width=16)  
  4.                ->  Nested Loop  (cost=0.00..16.56 rows=1 width=24)  
  5.                      ->  Index Scan using jcid_customerinstallationid on jivecustomerinstallationdeploy cid  (cost=0.00..8.27 rows=1 width=16)  
  6.                            Index Cond: (customerinstallationid = 660)  
  7.                      ->  Index Scan using jivedeployrequest_pk on jivedeployrequest dr  (cost=0.00..8.28 rows=1 width=40)  
  8.                            Index Cond: (deployrequestid = cid.deployrequestid)  
  9.                ->  Index Scan using jivedeploytype_pk on jivedeploytype dt  (cost=0.00..0.27 rows=1 width=8)  
  10.                      Index Cond: (deploytypeid = dr.deploytypeid)  
  11.                      Filter: ((typecode)::text <> 'disable-magic-admin'::text)  
  12.          ->  Index Scan using jivedeploystatus_pk on jivedeploystatus ds  (cost=0.00..0.29 rows=1 width=16)  
  13.                Index Cond: (dr.deploystatusid = deploystatusid)  
  14.                Filter: (((SubPlan 1))::text = 'inprocess'::text)  
  15.                SubPlan 1  
  16.                  ->  Result  (cost=0.00..0.02 rows=1 width=0) 


Somehow the subquery with CASE in WHERE clause is affecting the LEFT JOIN, and that causes the query to return extra rows.




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux