opt: plan lookup joins on indexes with virtual columns in more cases #75872
Description
Currently, GenerateLookupJoinsWithVirtualCols can only plan a lookup join on an index with a virtual column when there is a projected column with the same column ID as the virtual column ID in the table metadata. This is overly restrictive, preventing optimal plans in many cases.
For example, consider the virtual column created for an expression index:
exec-ddl
CREATE TABLE small (
k INT PRIMARY KEY,
a INT
);
----
exec-ddl
CREATE TABLE virt (
k INT PRIMARY KEY,
a INT,
INDEX ((a * 100))
);
----
norm
SELECT * FROM small INNER LOOKUP JOIN virt ON small.a = virt.a * 100
----
project
├── columns: k:1!null a:2!null b:3 k:6!null a:7 b:8
├── immutable
├── key: (1,6)
├── fd: (1)-->(2,3), (6)-->(7,8), (7)-->(2)
└── inner-join (hash)
├── columns: small.k:1!null small.a:2!null small.b:3 virt.k:6!null virt.a:7 virt.b:8 column12:12!null
├── flags: force lookup join (into right side)
├── immutable
├── key: (1,6)
├── fd: (1)-->(2,3), (6)-->(7,8), (7)-->(12), (2)==(12), (12)==(2)
├── scan small
│ ├── columns: small.k:1!null small.a:2 small.b:3
│ ├── key: (1)
│ └── fd: (1)-->(2,3)
├── project
│ ├── columns: column12:12 virt.k:6!null virt.a:7 virt.b:8
│ ├── immutable
│ ├── key: (6)
│ ├── fd: (6)-->(7,8), (7)-->(12)
│ ├── scan virt
│ │ ├── columns: virt.k:6!null virt.a:7 virt.b:8
│ │ ├── computed column expressions
│ │ │ └── crdb_internal_idx_expr:11
│ │ │ └── virt.a:7 * 100
│ │ ├── key: (6)
│ │ └── fd: (6)-->(7,8)
│ └── projections
│ └── virt.a:7 * 100 [as=column12:12, outer=(7), immutable]
└── filters
└── small.a:2 = column12:12 [outer=(2,12), constraints=(/2: (/NULL - ]; /12: (/NULL - ]), fd=(2)==(12), (12)==(2)]
Notice that in the canonical expression, the projection of the virtual column expression is column12:12, while the virtual computed column in virt's table metadat is crdb_internal_idx_expr:11. Because these are not the same column, a lookup join is not planned.
Metadata
Metadata
Assignees
Labels
Type
Projects
Status