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

     1  exec-ddl
     2  CREATE TABLE abcd (a INT, b INT, c INT, INDEX (a,b))
     3  ----
     4  
     5  exec-ddl
     6  CREATE TABLE small (m INT, n INT)
     7  ----
     8  
     9  exec-ddl
    10  ALTER TABLE small INJECT STATISTICS '[
    11    {
    12      "columns": ["m"],
    13      "created_at": "2018-01-01 1:00:00.00000+00:00",
    14      "row_count": 10,
    15      "distinct_count": 10
    16    }
    17  ]'
    18  ----
    19  
    20  # We can only test lookup stat generation when using non-covering indexes
    21  # (that's when we create a group with LookupJoin). We can compare the
    22  # logical properties against those of the top-level join.
    23  
    24  opt
    25  SELECT * FROM small JOIN abcd ON a=m
    26  ----
    27  inner-join (lookup abcd)
    28   ├── columns: m:1(int!null) n:2(int) a:4(int!null) b:5(int) c:6(int)
    29   ├── key columns: [7] = [7]
    30   ├── lookup columns are key
    31   ├── fd: (1)==(4), (4)==(1)
    32   ├── prune: (2,5,6)
    33   ├── inner-join (lookup abcd@secondary)
    34   │    ├── columns: m:1(int!null) n:2(int) a:4(int!null) b:5(int) abcd.rowid:7(int!null)
    35   │    ├── key columns: [1] = [4]
    36   │    ├── fd: (7)-->(4,5), (1)==(4), (4)==(1)
    37   │    ├── scan small
    38   │    │    ├── columns: m:1(int) n:2(int)
    39   │    │    └── prune: (1,2)
    40   │    └── filters (true)
    41   └── filters (true)
    42  
    43  # Filter that gets pushed down on both sides, but comes back into the ON
    44  # condition for the lookup side.
    45  opt
    46  SELECT * FROM small JOIN abcd ON a=m WHERE n > 2
    47  ----
    48  inner-join (lookup abcd)
    49   ├── columns: m:1(int!null) n:2(int!null) a:4(int!null) b:5(int) c:6(int)
    50   ├── key columns: [7] = [7]
    51   ├── lookup columns are key
    52   ├── fd: (1)==(4), (4)==(1)
    53   ├── prune: (5,6)
    54   ├── inner-join (lookup abcd@secondary)
    55   │    ├── columns: m:1(int!null) n:2(int!null) a:4(int!null) b:5(int) abcd.rowid:7(int!null)
    56   │    ├── key columns: [1] = [4]
    57   │    ├── fd: (7)-->(4,5), (1)==(4), (4)==(1)
    58   │    ├── select
    59   │    │    ├── columns: m:1(int) n:2(int!null)
    60   │    │    ├── prune: (1)
    61   │    │    ├── scan small
    62   │    │    │    ├── columns: m:1(int) n:2(int)
    63   │    │    │    └── prune: (1,2)
    64   │    │    └── filters
    65   │    │         └── gt [type=bool, outer=(2), constraints=(/2: [/3 - ]; tight)]
    66   │    │              ├── variable: n:2 [type=int]
    67   │    │              └── const: 2 [type=int]
    68   │    └── filters (true)
    69   └── filters (true)
    70  
    71  # Filter that applies to the right side and gets pulled back into the ON
    72  # condition.
    73  opt
    74  SELECT * FROM small JOIN abcd ON a=m WHERE b > 2
    75  ----
    76  inner-join (lookup abcd)
    77   ├── columns: m:1(int!null) n:2(int) a:4(int!null) b:5(int!null) c:6(int)
    78   ├── key columns: [7] = [7]
    79   ├── lookup columns are key
    80   ├── fd: (1)==(4), (4)==(1)
    81   ├── prune: (2,6)
    82   ├── inner-join (lookup abcd@secondary)
    83   │    ├── columns: m:1(int!null) n:2(int) a:4(int!null) b:5(int!null) abcd.rowid:7(int!null)
    84   │    ├── key columns: [1] = [4]
    85   │    ├── fd: (7)-->(4,5), (1)==(4), (4)==(1)
    86   │    ├── scan small
    87   │    │    ├── columns: m:1(int) n:2(int)
    88   │    │    └── prune: (1,2)
    89   │    └── filters
    90   │         └── gt [type=bool, outer=(5), constraints=(/5: [/3 - ]; tight)]
    91   │              ├── variable: b:5 [type=int]
    92   │              └── const: 2 [type=int]
    93   └── filters (true)
    94  
    95  # Filter that can only be applied after the primary index join.
    96  opt
    97  SELECT * FROM small JOIN abcd ON a=m WHERE c>2
    98  ----
    99  inner-join (lookup abcd)
   100   ├── columns: m:1(int!null) n:2(int) a:4(int!null) b:5(int) c:6(int!null)
   101   ├── key columns: [7] = [7]
   102   ├── lookup columns are key
   103   ├── fd: (1)==(4), (4)==(1)
   104   ├── prune: (2,5)
   105   ├── inner-join (lookup abcd@secondary)
   106   │    ├── columns: m:1(int!null) n:2(int) a:4(int!null) b:5(int) abcd.rowid:7(int!null)
   107   │    ├── key columns: [1] = [4]
   108   │    ├── fd: (7)-->(4,5), (1)==(4), (4)==(1)
   109   │    ├── scan small
   110   │    │    ├── columns: m:1(int) n:2(int)
   111   │    │    └── prune: (1,2)
   112   │    └── filters (true)
   113   └── filters
   114        └── gt [type=bool, outer=(6), constraints=(/6: [/3 - ]; tight)]
   115             ├── variable: c:6 [type=int]
   116             └── const: 2 [type=int]
   117  
   118  # Multiple equalities.
   119  opt
   120  SELECT * FROM small JOIN abcd ON a=m AND b=n WHERE c>2
   121  ----
   122  inner-join (lookup abcd)
   123   ├── columns: m:1(int!null) n:2(int!null) a:4(int!null) b:5(int!null) c:6(int!null)
   124   ├── key columns: [7] = [7]
   125   ├── lookup columns are key
   126   ├── fd: (1)==(4), (4)==(1), (2)==(5), (5)==(2)
   127   ├── inner-join (lookup abcd@secondary)
   128   │    ├── columns: m:1(int!null) n:2(int!null) a:4(int!null) b:5(int!null) abcd.rowid:7(int!null)
   129   │    ├── key columns: [1 2] = [4 5]
   130   │    ├── fd: (7)-->(4,5), (1)==(4), (4)==(1), (2)==(5), (5)==(2)
   131   │    ├── scan small
   132   │    │    ├── columns: m:1(int) n:2(int)
   133   │    │    └── prune: (1,2)
   134   │    └── filters (true)
   135   └── filters
   136        └── gt [type=bool, outer=(6), constraints=(/6: [/3 - ]; tight)]
   137             ├── variable: c:6 [type=int]
   138             └── const: 2 [type=int]