opt: wrong index chosen for point lookup

[justinj]

Haven't dug into this, but it seems wrong:

exec-ddl
CREATE TABLE x (
  a INT PRIMARY KEY,
  b INT,
  c INT,
  INDEX b_idx (b),
  INDEX c_idx (c)
)
----

opt
SELECT * FROM 
    x
WHERE
    a = 1 AND b = 2 AND c = 3
----
select
 ├── columns: a:1(int!null) b:2(int!null) c:3(int!null)
 ├── cardinality: [0 - 1]
 ├── key: ()
 ├── fd: ()-->(1-3)
 ├── index-join x
 │    ├── columns: a:1(int!null) b:2(int) c:3(int)
 │    ├── cardinality: [0 - 1]
 │    ├── key: ()
 │    ├── fd: ()-->(1-3)
 │    └── scan x@b_idx
 │         ├── columns: a:1(int!null) b:2(int!null)
 │         ├── constraint: /2/1: [/2/1 - /2/1]
 │         ├── cardinality: [0 - 1]
 │         ├── key: ()
 │         └── fd: ()-->(1,2)
 └── filters
      └── c = 3 [type=bool, outer=(3), constraints=(/3: [/3 - /3]; tight), fd=()-->(3)]

I suspect we're choosing the secondary index because it has more columns that match the condition, but since the lookup columns contain the primary key there's still only one result either way, so avoiding the index join would be preferable.

[RaduBerinde]

Can you show the output with atats? I believe the row count for the index would be smaller than 1, which explains things. Perhaps we want to add a constant cost for index join (a cost comparable to scanning 1 row).

[justinj]

with stats:

exec-ddl
CREATE TABLE x (
  a INT PRIMARY KEY,
  b INT,
  c INT,
  INDEX b_idx (b),
  INDEX c_idx (c)
)
----

opt format=show-all
SELECT * FROM 
    x
WHERE
    a = 1 AND b = 2 AND c = 3
----
select
 ├── columns: a:1(int!null) b:2(int!null) c:3(int!null)
 ├── cardinality: [0 - 1]
 ├── stats: [rows=0.9801, distinct(1)=0.9801, null(1)=0, distinct(2)=0.9801, null(2)=0, distinct(3)=0.9801, null(3)=0]
 ├── cost: 0.080688
 ├── key: ()
 ├── fd: ()-->(1-3)
 ├── interesting orderings: (+1) (+2,+1)
 ├── index-join x
 │    ├── columns: t.public.x.a:1(int!null) t.public.x.b:2(int) t.public.x.c:3(int)
 │    ├── cardinality: [0 - 1]
 │    ├── stats: [rows=0.0099]
 │    ├── cost: 0.070589
 │    ├── key: ()
 │    ├── fd: ()-->(1-3)
 │    ├── interesting orderings: (+1) (+2,+1)
 │    └── scan t.public.x@b_idx
 │         ├── columns: t.public.x.a:1(int!null) t.public.x.b:2(int!null)
 │         ├── constraint: /2/1: [/2/1 - /2/1]
 │         ├── cardinality: [0 - 1]
 │         ├── stats: [rows=0.0099, distinct(1)=0.0099, null(1)=0, distinct(2)=0.0099, null(2)=0]
 │         ├── cost: 0.020296
 │         ├── key: ()
 │         ├── fd: ()-->(1,2)
 │         ├── prune: (1,2)
 │         └── interesting orderings: (+1) (+2,+1)
 └── filters
      └── eq [type=bool, outer=(3), constraints=(/3: [/3 - /3]; tight), fd=()-->(3)]
           ├── variable: t.public.x.c [type=int]
           └── const: 3 [type=int]

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
5 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[rytaft]

We now plan a constrained primary index scan for this query.