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.
