execution plan is wrong, or the query ?

From: Alex Burkoff <alex(dot)burkoff(at)jivesoftware(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: execution plan is wrong, or the query ?
Date: 2012-12-10 22:38:51
Message-ID: E3E0FC08A807464C8DF38036F52A78FA035E4B3C@mbx4.jiveland.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Sullivan 2012-12-10 22:57:50 Re: Problem with aborting entire transactions on error
Previous Message Andreas Joseph Krogh 2012-12-10 22:06:57 Re: to_tsquery and to_tsvector .. problem with Y