Complete different plans (and execution) for very similar queries
HelloI have been struggling to understand the behaviour of a query which is very slow (40 sec) but becomes really fast if I slightly modify it:
Original version
select *
from
( select this_.id as y0_, this_.UPDATEDDATE as y1_
from Document this_ inner join DOCUMENT_STATUS status1_ on this_.STATUS_ID=status1_.id
left outer join DOCUMENT_TYPE type2_ on this_.TYPE_ID=type2_.id
where
this_.VERSION = (select max(doc2_.VERSION) as y0_
from Document doc2_ where doc2_.PROJECT_ID=this_.PROJECT_ID and doc2_.REFERENCE=this_.REFERENCE )
and
this_.PROJECT_ID=203
and
( this_.CREATEDBY=664
or
exists
(select doc_folder.document_id from DOCUMENT_FOLDER doc_folder
Original version
select *
from
( select this_.id as y0_, this_.UPDATEDDATE as y1_
from Document this_ inner join DOCUMENT_STATUS status1_ on this_.STATUS_ID=status1_.id
left outer join DOCUMENT_TYPE type2_ on this_.TYPE_ID=type2_.id
where
this_.VERSION = (select max(doc2_.VERSION) as y0_
from Document doc2_ where doc2_.PROJECT_ID=this_.PROJECT_ID and doc2_.REFERENCE=this_.REFERENCE )
and
this_.PROJECT_ID=203
and
( this_.CREATEDBY=664
or
exists
(select doc_folder.document_id from DOCUMENT_FOLDER doc_folder
0