execution plan is wrong, or the query ?

[Alex Burkoff <alex.burkoff@xxxxxxxxxxxxxxxx>]

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.