Bug 115102 - Unneccessary outer join are impacting query performance
Unneccessary outer join are impacting query performance
Status: CLOSED RAWHIDE
Product: Red Hat Web Application Framework
Classification: Retired
Component: persistence (Show other bugs)
nightly
All Linux
medium Severity high
: ---
: ---
Assigned To: Archit Shah
Jon Orris
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2004-02-06 12:48 EST by Daniel Berrange
Modified: 2007-04-18 13:02 EDT (History)
0 users

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2006-09-02 13:47:04 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
Explain plans (6.13 KB, text/plain)
2004-02-06 12:48 EST, Daniel Berrange
no flags Details

  None (edit)
Description Daniel Berrange 2004-02-06 12:48:21 EST
Description of problem:
Whenever a path is added to a query that crosses an 0..1 or 0..n
association, persistence changes to using an outer join instead or a
regular join. In a great many cases this is uneccessary because there
is typically a condition in the where clause that makes the outer join
behave in the same way as a regular join. The presense of the outer
join is making PostgreSQL choose a horrifically worse query plan to
the effect that a simple query changes from 22ms to 350ms.

For example, if I have have a model:

object type Term extends ACSObject {
    Integer[1..1] uniqueID = trm_terms.unique_id INTEGER;
    Boolean[1..1] inAtoZ = trm_terms.is_atoz BIT;

    component Category[1..1] \model = join trm_terms.model_category_id 
                                       to cat_categories.category_id;
}

If I now do:


   ACSObject object = getObject(state);
   DataCollection terms = SessionManager.getSession()
     .retrieve(Term.BASE_DATA_OBJECT_TYPE);
        
   terms.addEqualsFilter("model.childObjects.id", object.getID());

ie, get all terms corresponding to assigned categories on the object

It generates a query:

select tt.term_id as c_1,
       ao.object_type as c_2,
       ao.display_name as c_3,
       ao.default_domain_class as c_4,
       tt.unique_id as c_5,
       tt.is_atoz as c_6,
       tt.shortcut as c_7,
       tt.is_preferred as c_8,
       model__cc.name as c_9,
       tt.model_category_id as c_10,
       tt.domain as c_11,
       domain__td.url as c_12
from trm_terms tt
     join acs_objects ao on ao.object_id = tt.term_id
     join cat_categories model__cc on model__cc.category_id =
tt.model_category_id
     join trm_domains domain__td on domain__td.key = tt.domain
     left join cat_object_category_map model__cocm on
model__cocm.category_id = tt.model_category_id
where (model__cocm.object_id = '9004');


This left join against the cat_object_category_map is redundant becuse
the of the constraining where clause means that no rows will be
returned at all if there wasn't a matching row in the
cat_object_catgory_map. I'll attach an SQL file with the explain plans
indicating the problem. 

Version-Release number of selected component (if applicable):


How reproducible:


Steps to Reproduce:
1.
2.
3.
  
Actual results:


Expected results:


Additional info:
Comment 1 Daniel Berrange 2004-02-06 12:48:52 EST
Created attachment 97518 [details]
Explain plans
Comment 2 Aram Kananov 2004-02-06 12:51:28 EST
Outer joins used in pg to explicitely control exec plan,
so it is better do not use them blindly.

http://www.postgresql.org/docs/7.3/static/explicit-joins.html
Comment 3 Richard Li 2004-02-10 17:03:35 EST
This is addressed by the new OQL query analyzer that is being
developed. Once that branch is landed, we will need to update the
wrapper layer to generate OQL queries in order to completely solve
this problem.
Comment 4 Archit Shah 2004-04-06 10:24:00 EDT
fixed with 41648, which landed the appropriate changes to wrapper layer
Comment 5 David Lawrence 2006-07-17 23:26:44 EDT
QA_READY has been deprecated in favor of ON_QA. Please use ON_QA in the future.
Moving to ON_QA.
Comment 6 Daniel Berrange 2006-09-02 13:47:04 EDT
Closing old tickets

Note You need to log in before you can comment on or make changes to this bug.