cockroachdb / cockroachdb/cockroach

Plan prepared queries having many joins with ReorderJoins disabled

Open
#99,885 0 comments 0 reactions 0 assignees View on GitHub
A-generic-query-plans A-prepared-stmts C-performance T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Is your feature request related to a problem? Please describe.**
Queries with placeholders and many joins often take a long time to re-plan, often due to reordering joins.
Query planning time often exceeds execution time.

**Describe the solution you'd like**
When running EXECUTE of a prepared statement, and the number of joins in the query is high (above 5 or 6), re-optimize the query with placeholders assigned (as usual), but with the `ReorderJoins` rule disabled, and find the new estimated execution cost.
Estimate the cost of planning the query with `ReorderJoins` enabled . If the estimated execution cost exceeds some fraction of the estimated planning cost, optimize the plan again, but with `ReorderJoins` enabled.
This way a "good enough" query plan can be used in many cases instead of spending more time in planning than in execution.
Note that the first time a query is planned with placeholders we may need allow `ReorderJoins` to be done so that a good baseline join order can be found.

**Describe alternatives you've considered**
None

Jira issue: CRDB-26114

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.