github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/rules/manual (about)

     1  exec-ddl
     2  CREATE TABLE abcde (
     3      a INT PRIMARY KEY,
     4      b INT,
     5      c INT,
     6      d INT,
     7      e INT,
     8      UNIQUE INDEX bc (b, c)
     9  )
    10  ----
    11  
    12  # --------------------------------------------------
    13  # SimplifyRootOrdering
    14  # --------------------------------------------------
    15  opt
    16  SELECT * FROM abcde ORDER BY a, b, c, d
    17  ----
    18  scan abcde
    19   ├── columns: a:1!null b:2 c:3 d:4 e:5
    20   ├── key: (1)
    21   ├── fd: (1)-->(2-5), (2,3)~~>(1,4,5)
    22   └── ordering: +1
    23  
    24  opt
    25  SELECT * FROM abcde ORDER BY b, c, a, d LIMIT 10
    26  ----
    27  index-join abcde
    28   ├── columns: a:1!null b:2 c:3 d:4 e:5
    29   ├── cardinality: [0 - 10]
    30   ├── key: (1)
    31   ├── fd: (1)-->(2-5), (2,3)~~>(1,4,5)
    32   ├── ordering: +2,+3,+1
    33   └── scan abcde@bc
    34        ├── columns: a:1!null b:2 c:3
    35        ├── limit: 10
    36        ├── key: (1)
    37        ├── fd: (1)-->(2,3), (2,3)~~>(1)
    38        └── ordering: +2,+3,+1
    39  
    40  # d is implied by b+c, so needs to be removed from ORDER BY and scan projection.
    41  opt
    42  SELECT a FROM abcde WHERE b=1 AND c IS NOT NULL ORDER BY c, d
    43  ----
    44  project
    45   ├── columns: a:1!null  [hidden: c:3!null]
    46   ├── key: (1)
    47   ├── fd: (1)-->(3), (3)-->(1)
    48   ├── ordering: +3
    49   └── scan abcde@bc
    50        ├── columns: a:1!null b:2!null c:3!null
    51        ├── constraint: /2/3: (/1/NULL - /1]
    52        ├── key: (1)
    53        ├── fd: ()-->(2), (1)-->(3), (3)-->(1)
    54        └── ordering: +3 opt(2) [actual: +3]
    55  
    56  # d is required for the ordering, so requires a lookup-join.
    57  opt
    58  SELECT a FROM abcde WHERE b=1 ORDER BY c, b, d
    59  ----
    60  sort
    61   ├── columns: a:1!null  [hidden: b:2!null c:3 d:4]
    62   ├── key: (1)
    63   ├── fd: ()-->(2), (1)-->(3,4), (2,3)~~>(1,4)
    64   ├── ordering: +3,+4 opt(2) [actual: +3,+4]
    65   └── index-join abcde
    66        ├── columns: a:1!null b:2!null c:3 d:4
    67        ├── key: (1)
    68        ├── fd: ()-->(2), (1)-->(3,4), (2,3)~~>(1,4)
    69        └── scan abcde@bc
    70             ├── columns: a:1!null b:2!null c:3
    71             ├── constraint: /2/3: [/1 - /1]
    72             ├── key: (1)
    73             └── fd: ()-->(2), (1)-->(3), (2,3)~~>(1)
    74  
    75  # --------------------------------------------------
    76  # PruneRootCols
    77  # --------------------------------------------------
    78  # Remove functionally dependent column that's only used in ordering, which
    79  # triggers an opportunity to prune output columns, which in turn makes the
    80  # lookup-join unnecessary.
    81  opt
    82  SELECT c FROM abcde ORDER BY b, c, a, d LIMIT 10
    83  ----
    84  scan abcde@bc
    85   ├── columns: c:3  [hidden: a:1!null b:2]
    86   ├── limit: 10
    87   ├── key: (1)
    88   ├── fd: (1)-->(2,3), (2,3)~~>(1)
    89   └── ordering: +2,+3,+1
    90  
    91  opt
    92  SELECT d FROM (SELECT d, d+1 AS one FROM abcde) GROUP BY d, one ORDER BY d, one
    93  ----
    94  sort
    95   ├── columns: d:4
    96   ├── key: (4)
    97   ├── ordering: +4
    98   └── distinct-on
    99        ├── columns: d:4
   100        ├── grouping columns: d:4
   101        ├── key: (4)
   102        └── scan abcde
   103             └── columns: d:4
   104  
   105  # --------------------------------------------------
   106  # SimplifyProjectOrdering
   107  # --------------------------------------------------
   108  # Filter "b", but do not use it in the projection or ORDER BY.
   109  opt
   110  SELECT a FROM abcde WHERE b=1 ORDER BY c
   111  ----
   112  project
   113   ├── columns: a:1!null  [hidden: c:3]
   114   ├── key: (1)
   115   ├── fd: (1)-->(3)
   116   ├── ordering: +3
   117   └── scan abcde@bc
   118        ├── columns: a:1!null b:2!null c:3
   119        ├── constraint: /2/3: [/1 - /1]
   120        ├── key: (1)
   121        ├── fd: ()-->(2), (1)-->(3), (2,3)~~>(1)
   122        └── ordering: +3 opt(2) [actual: +3]
   123  
   124  # Filter "b", but do not use it in the projection or ORDER BY. Add an additional
   125  # column to the ordering that triggers lookup-join.
   126  opt
   127  SELECT a FROM abcde WHERE b=1 ORDER BY c, d
   128  ----
   129  sort
   130   ├── columns: a:1!null  [hidden: c:3 d:4]
   131   ├── key: (1)
   132   ├── fd: (1)-->(3,4)
   133   ├── ordering: +3,+4
   134   └── project
   135        ├── columns: a:1!null c:3 d:4
   136        ├── key: (1)
   137        ├── fd: (1)-->(3,4)
   138        └── index-join abcde
   139             ├── columns: a:1!null b:2!null c:3 d:4
   140             ├── key: (1)
   141             ├── fd: ()-->(2), (1)-->(3,4), (2,3)~~>(1,4)
   142             └── scan abcde@bc
   143                  ├── columns: a:1!null b:2!null c:3
   144                  ├── constraint: /2/3: [/1 - /1]
   145                  ├── key: (1)
   146                  └── fd: ()-->(2), (1)-->(3), (2,3)~~>(1)