Jadon Hansell created CAY-2917:
----------------------------------
Summary: Add Qualifier Joins Before Depenent Table Joins
Key: CAY-2917
URL: https://issues.apache.org/jira/browse/CAY-2917
Project: Cayenne
Issue Type: Improvement
Reporter: Jadon Hansell
When a query is executed on the parent model in a vertical inheritance
scenario, the ColumnExtractorStage adds left joins to all dependent tables to
gather the necessary columns for the select. After that, the rest of the joins
for the query are added in the PrefetchNodeStage, QualifierTranslationStage,
etc. This can have a large performance impact when there are several dependent
tables.
For example, PostgreSQL's query planner will only attempt to reorder the first
8 joins (by default) to try to determine an optimal join order for the query.
With a small number of dependent tables, it will usually execute the joins
added by the QualifierTranslationStage first to narrow down the result set
before executing the dependent tables' left joins for the rest of the data.
However, if you have 8 or more dependent tables, a very inefficient query plan
is created. All the dependent tables' left joins are executed first, ballooning
the number of rows the query has to process until it gets to the qualifier
joins at the end. This can significantly reduce the speed of some queries.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
