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:
Created attachment 97518 [details] Explain plans
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
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.
fixed with 41648, which landed the appropriate changes to wrapper layer
QA_READY has been deprecated in favor of ON_QA. Please use ON_QA in the future. Moving to ON_QA.
Closing old tickets