opt: explore lookup join with IS NOT DISTINCT FROM equality
#100855
Labels
C-enhancement
Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
T-sql-queries
SQL Queries Team
Comments
mgartner
added
the
C-enhancement
mgartner
added a commit
to mgartner/cockroach
that referenced
this issue
Apr 12, 2023
Subqueries that are in equality expressions with a variable are now
hoisted. When these expressions exist in a filter, hoisting the subquery
can allow the main query to plan a lookup join, rather than an
inefficient full-table scan.
For example, consider the table and query:
CREATE TABLE t (
a INT,
INDEX (a)
);
SELECT * FROM t WHERE a = (SELECT max(a) FROM t);
Prior to this commit, the query plan for this query required a full
table scan:
select
├── columns: a:1
├── scan t@t_a_idx
│ ├── columns: a:1
│ └── constraint: /1/2: (/NULL - ]
└── filters
└── eq
├── a:1
└── subquery
└── scalar-group-by
├── columns: max:9
├── scan t@t_a_idx,rev
│ ├── columns: a:5
│ ├── constraint: /5/6: (/NULL - ]
│ └── limit: 1(rev)
└── aggregations
└── const-agg [as=max:9, outer=(5)]
└── a:5
By hoisting the subquery, the full table scan is replaced with a lookup
join:
project
├── columns: a:1
└── inner-join (lookup t@t_a_idx)
├── columns: a:1 max:9
├── key columns: [9] = [1]
├── scalar-group-by
│ ├── columns: max:9
│ ├── scan t@t_a_idx,rev
│ │ ├── columns: a:5
│ │ ├── constraint: /5/6: (/NULL - ]
│ │ └── limit: 1(rev)
│ └── aggregations
│ └── const-agg [as=max:9, outer=(5)]
│ └── a:5
└── filters (true)
This hoisting is enabled by default, but can be disabled by setting the
`optimizer_hoist_uncorrelated_equality_subqueries` session setting to
`false`.
Fixes cockroachdb#83392
Informs cockroachdb#51820
Informs cockroachdb#93829
Informs cockroachdb#100855
Release note (performance improvement): Queries that have subqueries in
equality expressions are now more efficiently planned by the optimizer.
mgartner
added a commit
to mgartner/cockroach
that referenced
this issue
Apr 17, 2023
Subqueries that are in equality expressions with a variable are now
hoisted. When these expressions exist in a filter, hoisting the subquery
can allow the main query to plan a lookup join, rather than an
inefficient full-table scan.
For example, consider the table and query:
CREATE TABLE t (
a INT,
INDEX (a)
);
SELECT * FROM t WHERE a = (SELECT max(a) FROM t);
Prior to this commit, the query plan for this query required a full
table scan:
select
├── columns: a:1
├── scan t@t_a_idx
│ ├── columns: a:1
│ └── constraint: /1/2: (/NULL - ]
└── filters
└── eq
├── a:1
└── subquery
└── scalar-group-by
├── columns: max:9
├── scan t@t_a_idx,rev
│ ├── columns: a:5
│ ├── constraint: /5/6: (/NULL - ]
│ └── limit: 1(rev)
└── aggregations
└── const-agg [as=max:9, outer=(5)]
└── a:5
By hoisting the subquery, the full table scan is replaced with a lookup
join:
project
├── columns: a:1
└── inner-join (lookup t@t_a_idx)
├── columns: a:1 max:9
├── key columns: [9] = [1]
├── scalar-group-by
│ ├── columns: max:9
│ ├── scan t@t_a_idx,rev
│ │ ├── columns: a:5
│ │ ├── constraint: /5/6: (/NULL - ]
│ │ └── limit: 1(rev)
│ └── aggregations
│ └── const-agg [as=max:9, outer=(5)]
│ └── a:5
└── filters (true)
This hoisting is enabled by default, but can be disabled by setting the
`optimizer_hoist_uncorrelated_equality_subqueries` session setting to
`false`.
Fixes cockroachdb#83392
Informs cockroachdb#51820
Informs cockroachdb#93829
Informs cockroachdb#100855
Release note (performance improvement): Queries that have subqueries in
equality expressions are now more efficiently planned by the optimizer.
craig bot
pushed a commit
that referenced
this issue
Apr 17, 2023
100881: opt: hoist uncorrelated equality subqueries r=mgartner a=mgartner
#### opt: hoist uncorrelated equality subqueries
Subqueries that are in equality expressions with a variable are now
hoisted. When these expressions exist in a filter, hoisting the subquery
can allow the main query to plan a lookup join, rather than an
inefficient full-table scan.
For example, consider the table and query:
CREATE TABLE t (
a INT,
INDEX (a)
);
SELECT * FROM t WHERE a = (SELECT max(a) FROM t);
Prior to this commit, the query plan for this query required a full
table scan:
select
├── columns: a:1
├── scan t@t_a_idx
│ ├── columns: a:1
│ └── constraint: /1/2: (/NULL - ]
└── filters
└── eq
├── a:1
└── subquery
└── scalar-group-by
├── columns: max:9
├── scan t@t_a_idx,rev
│ ├── columns: a:5
│ ├── constraint: /5/6: (/NULL - ]
│ └── limit: 1(rev)
└── aggregations
└── const-agg [as=max:9, outer=(5)]
└── a:5
By hoisting the subquery, the full table scan is replaced with a lookup
join:
project
├── columns: a:1
└── inner-join (lookup t@t_a_idx)
├── columns: a:1 max:9
├── key columns: [9] = [1]
├── scalar-group-by
│ ├── columns: max:9
│ ├── scan t@t_a_idx,rev
│ │ ├── columns: a:5
│ │ ├── constraint: /5/6: (/NULL - ]
│ │ └── limit: 1(rev)
│ └── aggregations
│ └── const-agg [as=max:9, outer=(5)]
│ └── a:5
└── filters (true)
This hoisting is enabled by default, but can be disabled by setting the
`optimizer_hoist_uncorrelated_equality_subqueries` session setting to
`false`.
Fixes #83392
Informs #51820
Informs #93829
Informs #100855
Release note (performance improvement): Queries that have subqueries in
equality expressions are now more efficiently planned by the optimizer.
Co-authored-by: Marcus Gartner <[email protected]>
blathers-crl bot
pushed a commit
that referenced
this issue
Apr 17, 2023
Subqueries that are in equality expressions with a variable are now
hoisted. When these expressions exist in a filter, hoisting the subquery
can allow the main query to plan a lookup join, rather than an
inefficient full-table scan.
For example, consider the table and query:
CREATE TABLE t (
a INT,
INDEX (a)
);
SELECT * FROM t WHERE a = (SELECT max(a) FROM t);
Prior to this commit, the query plan for this query required a full
table scan:
select
├── columns: a:1
├── scan t@t_a_idx
│ ├── columns: a:1
│ └── constraint: /1/2: (/NULL - ]
└── filters
└── eq
├── a:1
└── subquery
└── scalar-group-by
├── columns: max:9
├── scan t@t_a_idx,rev
│ ├── columns: a:5
│ ├── constraint: /5/6: (/NULL - ]
│ └── limit: 1(rev)
└── aggregations
└── const-agg [as=max:9, outer=(5)]
└── a:5
By hoisting the subquery, the full table scan is replaced with a lookup
join:
project
├── columns: a:1
└── inner-join (lookup t@t_a_idx)
├── columns: a:1 max:9
├── key columns: [9] = [1]
├── scalar-group-by
│ ├── columns: max:9
│ ├── scan t@t_a_idx,rev
│ │ ├── columns: a:5
│ │ ├── constraint: /5/6: (/NULL - ]
│ │ └── limit: 1(rev)
│ └── aggregations
│ └── const-agg [as=max:9, outer=(5)]
│ └── a:5
└── filters (true)
This hoisting is enabled by default, but can be disabled by setting the
`optimizer_hoist_uncorrelated_equality_subqueries` session setting to
`false`.
Fixes #83392
Informs #51820
Informs #93829
Informs #100855
Release note (performance improvement): Queries that have subqueries in
equality expressions are now more efficiently planned by the optimizer.
mgartner
added a commit
to mgartner/cockroach
that referenced
this issue
Apr 18, 2023
Subqueries that are in equality expressions with a variable are now
hoisted. When these expressions exist in a filter, hoisting the subquery
can allow the main query to plan a lookup join, rather than an
inefficient full-table scan.
For example, consider the table and query:
CREATE TABLE t (
a INT,
INDEX (a)
);
SELECT * FROM t WHERE a = (SELECT max(a) FROM t);
Prior to this commit, the query plan for this query required a full
table scan:
select
├── columns: a:1
├── scan t@t_a_idx
│ ├── columns: a:1
│ └── constraint: /1/2: (/NULL - ]
└── filters
└── eq
├── a:1
└── subquery
└── scalar-group-by
├── columns: max:9
├── scan t@t_a_idx,rev
│ ├── columns: a:5
│ ├── constraint: /5/6: (/NULL - ]
│ └── limit: 1(rev)
└── aggregations
└── const-agg [as=max:9, outer=(5)]
└── a:5
By hoisting the subquery, the full table scan is replaced with a lookup
join:
project
├── columns: a:1
└── inner-join (lookup t@t_a_idx)
├── columns: a:1 max:9
├── key columns: [9] = [1]
├── scalar-group-by
│ ├── columns: max:9
│ ├── scan t@t_a_idx,rev
│ │ ├── columns: a:5
│ │ ├── constraint: /5/6: (/NULL - ]
│ │ └── limit: 1(rev)
│ └── aggregations
│ └── const-agg [as=max:9, outer=(5)]
│ └── a:5
└── filters (true)
This hoisting is enabled by default, but can be disabled by setting the
`optimizer_hoist_uncorrelated_equality_subqueries` session setting to
`false`.
Fixes cockroachdb#83392
Informs cockroachdb#51820
Informs cockroachdb#93829
Informs cockroachdb#100855
Release note (performance improvement): Queries that have subqueries in
equality expressions are now more efficiently planned by the optimizer.
mgartner
added a commit
to mgartner/cockroach
that referenced
this issue
Apr 18, 2023
Subqueries that are in equality expressions with a variable are now
hoisted. When these expressions exist in a filter, hoisting the subquery
can allow the main query to plan a lookup join, rather than an
inefficient full-table scan.
For example, consider the table and query:
CREATE TABLE t (
a INT,
INDEX (a)
);
SELECT * FROM t WHERE a = (SELECT max(a) FROM t);
Prior to this commit, the query plan for this query required a full
table scan:
select
├── columns: a:1
├── scan t@t_a_idx
│ ├── columns: a:1
│ └── constraint: /1/2: (/NULL - ]
└── filters
└── eq
├── a:1
└── subquery
└── scalar-group-by
├── columns: max:9
├── scan t@t_a_idx,rev
│ ├── columns: a:5
│ ├── constraint: /5/6: (/NULL - ]
│ └── limit: 1(rev)
└── aggregations
└── const-agg [as=max:9, outer=(5)]
└── a:5
By hoisting the subquery, the full table scan is replaced with a lookup
join:
project
├── columns: a:1
└── inner-join (lookup t@t_a_idx)
├── columns: a:1 max:9
├── key columns: [9] = [1]
├── scalar-group-by
│ ├── columns: max:9
│ ├── scan t@t_a_idx,rev
│ │ ├── columns: a:5
│ │ ├── constraint: /5/6: (/NULL - ]
│ │ └── limit: 1(rev)
│ └── aggregations
│ └── const-agg [as=max:9, outer=(5)]
│ └── a:5
└── filters (true)
This hoisting is disabled by default, but can be enabled by setting the
`optimizer_hoist_uncorrelated_equality_subqueries` session setting to
`true`.
Fixes cockroachdb#83392
Informs cockroachdb#51820
Informs cockroachdb#93829
Informs cockroachdb#100855
Release note (performance improvement): Queries that have subqueries in
equality expressions are now more efficiently planned by the optimizer when
`optimizer_hoist_uncorrelated_equality_subqueries` is set to `true`.
mgartner
added a commit
to mgartner/cockroach
that referenced
this issue
Apr 18, 2023
Subqueries that are in equality expressions with a variable are now
hoisted. When these expressions exist in a filter, hoisting the subquery
can allow the main query to plan a lookup join, rather than an
inefficient full-table scan.
For example, consider the table and query:
CREATE TABLE t (
a INT,
INDEX (a)
);
SELECT * FROM t WHERE a = (SELECT max(a) FROM t);
Prior to this commit, the query plan for this query required a full
table scan:
select
├── columns: a:1
├── scan t@t_a_idx
│ ├── columns: a:1
│ └── constraint: /1/2: (/NULL - ]
└── filters
└── eq
├── a:1
└── subquery
└── scalar-group-by
├── columns: max:9
├── scan t@t_a_idx,rev
│ ├── columns: a:5
│ ├── constraint: /5/6: (/NULL - ]
│ └── limit: 1(rev)
└── aggregations
└── const-agg [as=max:9, outer=(5)]
└── a:5
By hoisting the subquery, the full table scan is replaced with a lookup
join:
project
├── columns: a:1
└── inner-join (lookup t@t_a_idx)
├── columns: a:1 max:9
├── key columns: [9] = [1]
├── scalar-group-by
│ ├── columns: max:9
│ ├── scan t@t_a_idx,rev
│ │ ├── columns: a:5
│ │ ├── constraint: /5/6: (/NULL - ]
│ │ └── limit: 1(rev)
│ └── aggregations
│ └── const-agg [as=max:9, outer=(5)]
│ └── a:5
└── filters (true)
This hoisting is disabled by default, but can be enabled by setting the
`optimizer_hoist_uncorrelated_equality_subqueries` session setting to
`true`.
Fixes cockroachdb#83392
Informs cockroachdb#51820
Informs cockroachdb#93829
Informs cockroachdb#100855
Release note (performance improvement): Queries that have subqueries in
equality expressions are now more efficiently planned by the optimizer when
`optimizer_hoist_uncorrelated_equality_subqueries` is set to `true`.
mgartner
added a commit
that referenced
this issue
Apr 18, 2023
Subqueries that are in equality expressions with a variable are now
hoisted. When these expressions exist in a filter, hoisting the subquery
can allow the main query to plan a lookup join, rather than an
inefficient full-table scan.
For example, consider the table and query:
CREATE TABLE t (
a INT,
INDEX (a)
);
SELECT * FROM t WHERE a = (SELECT max(a) FROM t);
Prior to this commit, the query plan for this query required a full
table scan:
select
├── columns: a:1
├── scan t@t_a_idx
│ ├── columns: a:1
│ └── constraint: /1/2: (/NULL - ]
└── filters
└── eq
├── a:1
└── subquery
└── scalar-group-by
├── columns: max:9
├── scan t@t_a_idx,rev
│ ├── columns: a:5
│ ├── constraint: /5/6: (/NULL - ]
│ └── limit: 1(rev)
└── aggregations
└── const-agg [as=max:9, outer=(5)]
└── a:5
By hoisting the subquery, the full table scan is replaced with a lookup
join:
project
├── columns: a:1
└── inner-join (lookup t@t_a_idx)
├── columns: a:1 max:9
├── key columns: [9] = [1]
├── scalar-group-by
│ ├── columns: max:9
│ ├── scan t@t_a_idx,rev
│ │ ├── columns: a:5
│ │ ├── constraint: /5/6: (/NULL - ]
│ │ └── limit: 1(rev)
│ └── aggregations
│ └── const-agg [as=max:9, outer=(5)]
│ └── a:5
└── filters (true)
This hoisting is disabled by default, but can be enabled by setting the
`optimizer_hoist_uncorrelated_equality_subqueries` session setting to
`true`.
Fixes #83392
Informs #51820
Informs #93829
Informs #100855
Release note (performance improvement): Queries that have subqueries in
equality expressions are now more efficiently planned by the optimizer when
`optimizer_hoist_uncorrelated_equality_subqueries` is set to `true`.
mgartner
added a commit
to mgartner/cockroach
that referenced
this issue
Apr 20, 2023
Subqueries that are in equality expressions with a variable are now
hoisted. When these expressions exist in a filter, hoisting the subquery
can allow the main query to plan a lookup join, rather than an
inefficient full-table scan.
For example, consider the table and query:
CREATE TABLE t (
a INT,
INDEX (a)
);
SELECT * FROM t WHERE a = (SELECT max(a) FROM t);
Prior to this commit, the query plan for this query required a full
table scan:
select
├── columns: a:1
├── scan t@t_a_idx
│ ├── columns: a:1
│ └── constraint: /1/2: (/NULL - ]
└── filters
└── eq
├── a:1
└── subquery
└── scalar-group-by
├── columns: max:9
├── scan t@t_a_idx,rev
│ ├── columns: a:5
│ ├── constraint: /5/6: (/NULL - ]
│ └── limit: 1(rev)
└── aggregations
└── const-agg [as=max:9, outer=(5)]
└── a:5
By hoisting the subquery, the full table scan is replaced with a lookup
join:
project
├── columns: a:1
└── inner-join (lookup t@t_a_idx)
├── columns: a:1 max:9
├── key columns: [9] = [1]
├── scalar-group-by
│ ├── columns: max:9
│ ├── scan t@t_a_idx,rev
│ │ ├── columns: a:5
│ │ ├── constraint: /5/6: (/NULL - ]
│ │ └── limit: 1(rev)
│ └── aggregations
│ └── const-agg [as=max:9, outer=(5)]
│ └── a:5
└── filters (true)
This hoisting is disabled by default, but can be enabled by setting the
`optimizer_hoist_uncorrelated_equality_subqueries` session setting to
`true`.
Fixes cockroachdb#83392
Informs cockroachdb#51820
Informs cockroachdb#93829
Informs cockroachdb#100855
Release note (performance improvement): Queries that have subqueries in
equality expressions are now more efficiently planned by the optimizer when
`optimizer_hoist_uncorrelated_equality_subqueries` is set to `true`.
mgartner
added a commit
to mgartner/cockroach
that referenced
this issue
Apr 21, 2023
Subqueries that are in equality expressions with a variable are now
hoisted. When these expressions exist in a filter, hoisting the subquery
can allow the main query to plan a lookup join, rather than an
inefficient full-table scan.
For example, consider the table and query:
CREATE TABLE t (
a INT,
INDEX (a)
);
SELECT * FROM t WHERE a = (SELECT max(a) FROM t);
Prior to this commit, the query plan for this query required a full
table scan:
select
├── columns: a:1
├── scan t@t_a_idx
│ ├── columns: a:1
│ └── constraint: /1/2: (/NULL - ]
└── filters
└── eq
├── a:1
└── subquery
└── scalar-group-by
├── columns: max:9
├── scan t@t_a_idx,rev
│ ├── columns: a:5
│ ├── constraint: /5/6: (/NULL - ]
│ └── limit: 1(rev)
└── aggregations
└── const-agg [as=max:9, outer=(5)]
└── a:5
By hoisting the subquery, the full table scan is replaced with a lookup
join:
project
├── columns: a:1
└── inner-join (lookup t@t_a_idx)
├── columns: a:1 max:9
├── key columns: [9] = [1]
├── scalar-group-by
│ ├── columns: max:9
│ ├── scan t@t_a_idx,rev
│ │ ├── columns: a:5
│ │ ├── constraint: /5/6: (/NULL - ]
│ │ └── limit: 1(rev)
│ └── aggregations
│ └── const-agg [as=max:9, outer=(5)]
│ └── a:5
└── filters (true)
This hoisting is disabled by default, but can be enabled by setting the
`optimizer_hoist_uncorrelated_equality_subqueries` session setting to
`true`.
Fixes cockroachdb#83392
Informs cockroachdb#51820
Informs cockroachdb#93829
Informs cockroachdb#100855
Release note (performance improvement): Queries that have subqueries in
equality expressions are now more efficiently planned by the optimizer when
`optimizer_hoist_uncorrelated_equality_subqueries` is set to `true`.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
The optimizer does not currently explore lookup joins when the join condition contains an equality with an
IS NOT DISTINCT FROMexpression:I believe a lookup join is possible here, so we should explore one.
Jira issue: CRDB-26655
The text was updated successfully, but these errors were encountered: