github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/logprops/index-join (about)

     1  exec-ddl
     2  CREATE TABLE a (
     3    x INT PRIMARY KEY,
     4    y INT,
     5    s STRING,
     6    d DECIMAL NOT NULL,
     7    UNIQUE (s DESC, d),
     8    UNIQUE (y, s)
     9  )
    10  ----
    11  
    12  exec-ddl
    13  CREATE TABLE abc (
    14    a INT,
    15    b INT,
    16    c INT,
    17    d INT,
    18    PRIMARY KEY (a, b),
    19    INDEX (c)
    20  )
    21  ----
    22  
    23  # In order to actually create new logical props for the index join, we need to
    24  # call ConstructLookupJoin, which only happens when where is a remaining filter.
    25  opt
    26  SELECT * FROM a WHERE s = 'foo' AND x + y = 10
    27  ----
    28  select
    29   ├── columns: x:1(int!null) y:2(int) s:3(string!null) d:4(decimal!null)
    30   ├── key: (1)
    31   ├── fd: ()-->(3), (1)-->(2,4), (4)-->(1,2), (2,3)~~>(1,4)
    32   ├── prune: (4)
    33   ├── interesting orderings: (+1) (-3,+4,+1)
    34   ├── index-join a
    35   │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
    36   │    ├── key: (1)
    37   │    ├── fd: ()-->(3), (1)-->(2,4), (4)-->(1), (3,4)~~>(1,2), (2,3)~~>(1,4)
    38   │    ├── interesting orderings: (+1) (-3,+4,+1)
    39   │    └── scan a@secondary
    40   │         ├── columns: x:1(int!null) s:3(string!null) d:4(decimal!null)
    41   │         ├── constraint: /-3/4: [/'foo' - /'foo']
    42   │         ├── key: (1)
    43   │         ├── fd: ()-->(3), (1)-->(4), (4)-->(1)
    44   │         ├── prune: (1,3,4)
    45   │         └── interesting orderings: (+1) (-3,+4,+1)
    46   └── filters
    47        └── eq [type=bool, outer=(1,2)]
    48             ├── plus [type=int]
    49             │    ├── variable: x:1 [type=int]
    50             │    └── variable: y:2 [type=int]
    51             └── const: 10 [type=int]
    52  
    53  opt
    54  SELECT y FROM a WHERE s = 'foo' AND x + y = 10
    55  ----
    56  project
    57   ├── columns: y:2(int)
    58   ├── prune: (2)
    59   └── select
    60        ├── columns: x:1(int!null) y:2(int) s:3(string!null)
    61        ├── key: (1)
    62        ├── fd: ()-->(3), (1)-->(2), (2,3)~~>(1)
    63        ├── interesting orderings: (+1) (-3)
    64        ├── index-join a
    65        │    ├── columns: x:1(int!null) y:2(int) s:3(string)
    66        │    ├── key: (1)
    67        │    ├── fd: ()-->(3), (1)-->(2), (2,3)~~>(1)
    68        │    ├── interesting orderings: (+1) (-3)
    69        │    └── scan a@secondary
    70        │         ├── columns: x:1(int!null) s:3(string!null)
    71        │         ├── constraint: /-3/4: [/'foo' - /'foo']
    72        │         ├── key: (1)
    73        │         ├── fd: ()-->(3)
    74        │         ├── prune: (1,3)
    75        │         └── interesting orderings: (+1) (-3)
    76        └── filters
    77             └── eq [type=bool, outer=(1,2)]
    78                  ├── plus [type=int]
    79                  │    ├── variable: x:1 [type=int]
    80                  │    └── variable: y:2 [type=int]
    81                  └── const: 10 [type=int]
    82  
    83  # Use secondary index to join to multi-valued primary index, but project only
    84  # a subset of the primary columns.
    85  opt
    86  SELECT b, c, d FROM abc WHERE c=1 AND d=2
    87  ----
    88  select
    89   ├── columns: b:2(int!null) c:3(int!null) d:4(int!null)
    90   ├── fd: ()-->(3,4)
    91   ├── prune: (2)
    92   ├── interesting orderings: (+1,+2) (+3,+1,+2)
    93   ├── index-join abc
    94   │    ├── columns: b:2(int!null) c:3(int) d:4(int)
    95   │    ├── fd: ()-->(3)
    96   │    ├── interesting orderings: (+1,+2) (+3,+1,+2)
    97   │    └── scan abc@secondary
    98   │         ├── columns: a:1(int!null) b:2(int!null) c:3(int!null)
    99   │         ├── constraint: /3/1/2: [/1 - /1]
   100   │         ├── key: (1,2)
   101   │         ├── fd: ()-->(3)
   102   │         ├── prune: (1-3)
   103   │         └── interesting orderings: (+1,+2) (+3,+1,+2)
   104   └── filters
   105        └── eq [type=bool, outer=(4), constraints=(/4: [/2 - /2]; tight), fd=()-->(4)]
   106             ├── variable: d:4 [type=int]
   107             └── const: 2 [type=int]