Обсуждение: execution plan is wrong, or the query ?
Folks,
I have a following query that used to work as intended on 8.3.5 :
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 :
- Aggregate (cost=1178.30..1178.31 rows=1 width=0)
- -> Nested Loop (cost=6.19..1178.29 rows=1 width=0)
- -> Nested Loop Left Join (cost=6.19..1178.01 rows=1 width=8)
- Join Filter: (ci.installationtypeid = it.installationtypeid)
- -> Nested Loop Left Join (cost=6.19..1176.61 rows=1 width=16)
- -> Nested Loop Left Join (cost=6.19..1168.25 rows=1 width=16)
- -> Hash Left Join (cost=6.19..1167.97 rows=1 width=24)
- Hash Cond: (dr.deploystatusid = ds.deploystatusid)
- Filter: (((subplan))::text = 'inprocess'::text)
- -> Nested Loop (cost=5.08..1163.12 rows=103 width=40)
- -> Bitmap Heap Scan on jivecustomerinstallationdeploy cid (cost=5.08..315.56 rows=103 width=16)
- Recheck Cond: (customerinstallationid = 660)
- -> Bitmap Index Scan on jcid_customerinstallationid (cost=0.00..5.05 rows=103 width=0)
- Index Cond: (customerinstallationid = 660)
- -> Index Scan using jivedeployrequest_pk on jivedeployrequest dr (cost=0.00..8.22 rows=1 width=40)
- Index Cond: (dr.deployrequestid = cid.deployrequestid)
- -> Hash (cost=1.05..1.05 rows=5 width=16)
- -> Seq Scan on jivedeploystatus ds (cost=0.00..1.05 rows=5 width=16)
- SubPlan
- -> Result (cost=0.00..0.02 rows=1 width=0)
- -> Index Scan using jiveclouduser_pk on jiveclouduser cu (cost=0.00..0.27 rows=1 width=8)
- Index Cond: (dr.clouduserid = cu.clouduserid)
- -> Index Scan using jivecustomerinstallation_pk on jivecustomerinstallation ci (cost=0.00..8.34 rows=1 width=16)
- Index Cond: ((ci.customerinstallationid = 660) AND (cid.customerinstallationid = ci.customerinstallationid))
- -> Seq Scan on jiveinstallationtype it (cost=0.00..1.18 rows=18 width=8)
- -> Index Scan using jivedeploytype_pk on jivedeploytype dt (cost=0.00..0.27 rows=1 width=8)
- Index Cond: (dt.deploytypeid = dr.deploytypeid)
- 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 :
- Aggregate (cost=17.15..17.16 rows=1 width=0)
- -> Nested Loop Left Join (cost=0.00..17.15 rows=1 width=0)
- -> Nested Loop (cost=0.00..16.85 rows=1 width=16)
- -> Nested Loop (cost=0.00..16.56 rows=1 width=24)
- -> Index Scan using jcid_customerinstallationid on jivecustomerinstallationdeploy cid (cost=0.00..8.27 rows=1 width=16)
- Index Cond: (customerinstallationid = 660)
- -> Index Scan using jivedeployrequest_pk on jivedeployrequest dr (cost=0.00..8.28 rows=1 width=40)
- Index Cond: (deployrequestid = cid.deployrequestid)
- -> Index Scan using jivedeploytype_pk on jivedeploytype dt (cost=0.00..0.27 rows=1 width=8)
- Index Cond: (deploytypeid = dr.deploytypeid)
- Filter: ((typecode)::text <> 'disable-magic-admin'::text)
- -> Index Scan using jivedeploystatus_pk on jivedeploystatus ds (cost=0.00..0.29 rows=1 width=16)
- Index Cond: (dr.deploystatusid = deploystatusid)
- Filter: (((SubPlan 1))::text = 'inprocess'::text)
- SubPlan 1
- -> 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.
Alex Burkoff <alex.burkoff@jivesoftware.com> writes:
> I have a following query that used to work as intended on 8.3.5 :
> ...
> After upgrade to 9.2 the query doesn't return the same results any more, and the execution plan has changed :
You'd need to provide a self-contained test case if you want an informed
opinion on this. The odds that 8.3.5 was wrong and the newer result is
right are not negligible. (If you were comparing to a *current* 8.3
release I might be more prepared to assume that 9.2 is the one that is
broken.)
FWIW, it looks like the 9.2 plan has been simplified by outer-join
elimination, which implies that unique constraints on the join columns
matter. Possibly you've found a bug in that optimization, but without
a concrete test case it's impossible to be sure, let alone fix it.
regards, tom lane
I think I am just looking for an opinion on fundamentals. Can WHERE clause impact an OUTER JOIN ?
What I am seeing is that on 9.2 rows from the joined table are restricted prior to joining them with the rest
of the tables, and that leads to incorrect results. I have noticed such behaviour only when CASE is used
in the WHERE clause - straight text comparison does produce correct results.
Thanks!
________________________________________
From: Tom Lane [tgl@sss.pgh.pa.us]
Sent: Monday, December 10, 2012 6:09 PM
To: Alex Burkoff
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] execution plan is wrong, or the query ?
Alex Burkoff <alex.burkoff@jivesoftware.com> writes:
> I have a following query that used to work as intended on 8.3.5 :
> ...
> After upgrade to 9.2 the query doesn't return the same results any more, and the execution plan has changed :
You'd need to provide a self-contained test case if you want an informed
opinion on this. The odds that 8.3.5 was wrong and the newer result is
right are not negligible. (If you were comparing to a *current* 8.3
release I might be more prepared to assume that 9.2 is the one that is
broken.)
FWIW, it looks like the 9.2 plan has been simplified by outer-join
elimination, which implies that unique constraints on the join columns
matter. Possibly you've found a bug in that optimization, but without
a concrete test case it's impossible to be sure, let alone fix it.
regards, tom lane
Alex Burkoff <alex.burkoff@jivesoftware.com> writes: > I think I am just looking for an opinion on fundamentals. Can WHERE clause impact an OUTER JOIN ? Sure. I noticed for instance that your query had some outer joins that were simplified to plain joins as a result of strict WHERE clauses above them. That's not incorrect. > What I am seeing is that on 9.2 rows from the joined table are restricted prior to joining them with the rest > of the tables, and that leads to incorrect results. I have noticed such behaviour only when CASE is used > in the WHERE clause - straight text comparison does produce correct results. Oh? Hm, I wonder whether you are needing this fix: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=72a4231f0c80f213a4fa75356dc3c6b7c7419059 Although that bug exists in some form back to 7.4, 9.2 had a more obvious form of the disease, which is what led to its diagnosis. If you're not running 9.2.2, give that a try and see if it's better. regards, tom lane
Understood. Thank you! ________________________________________ From: Tom Lane [tgl@sss.pgh.pa.us] Sent: Tuesday, December 11, 2012 9:10 AM To: Alex Burkoff Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] execution plan is wrong, or the query ? Alex Burkoff <alex.burkoff@jivesoftware.com> writes: > I think I am just looking for an opinion on fundamentals. Can WHERE clause impact an OUTER JOIN ? Sure. I noticed for instance that your query had some outer joins that were simplified to plain joins as a result of strict WHERE clauses above them. That's not incorrect. > What I am seeing is that on 9.2 rows from the joined table are restricted prior to joining them with the rest > of the tables, and that leads to incorrect results. I have noticed such behaviour only when CASE is used > in the WHERE clause - straight text comparison does produce correct results. Oh? Hm, I wonder whether you are needing this fix: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=72a4231f0c80f213a4fa75356dc3c6b7c7419059 Although that bug exists in some form back to 7.4, 9.2 had a more obvious form of the disease, which is what led to its diagnosis. If you're not running 9.2.2, give that a try and see if it's better. regards, tom lane
Upgrade to 9.2.2 totally fixed the issue. Thanks again! ________________________________________ From: Tom Lane [tgl@sss.pgh.pa.us] Sent: Tuesday, December 11, 2012 9:10 AM To: Alex Burkoff Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] execution plan is wrong, or the query ? Alex Burkoff <alex.burkoff@jivesoftware.com> writes: > I think I am just looking for an opinion on fundamentals. Can WHERE clause impact an OUTER JOIN ? Sure. I noticed for instance that your query had some outer joins that were simplified to plain joins as a result of strict WHERE clauses above them. That's not incorrect. > What I am seeing is that on 9.2 rows from the joined table are restricted prior to joining them with the rest > of the tables, and that leads to incorrect results. I have noticed such behaviour only when CASE is used > in the WHERE clause - straight text comparison does produce correct results. Oh? Hm, I wonder whether you are needing this fix: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=72a4231f0c80f213a4fa75356dc3c6b7c7419059 Although that bug exists in some form back to 7.4, 9.2 had a more obvious form of the disease, which is what led to its diagnosis. If you're not running 9.2.2, give that a try and see if it's better. regards, tom lane