github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/prepare (about)

     1  # LogicTest: 5node
     2  
     3  statement ok
     4  CREATE TABLE ab (a INT PRIMARY KEY, b INT); INSERT INTO ab (a, b) VALUES (1, 10)
     5  
     6  ## Table index change: Add/remove index that query depends on, and ensure that
     7  ## the plan is recomputed each time.
     8  statement ok
     9  PREPARE change_index AS SELECT * FROM [EXPLAIN SELECT * FROM ab WHERE b=10]
    10  
    11  query TTT
    12  EXECUTE change_index
    13  ----
    14  ·     distributed  true
    15  ·     vectorized   true
    16  scan  ·            ·
    17  ·     table        ab@primary
    18  ·     spans        FULL SCAN
    19  ·     filter       b = 10
    20  
    21  statement ok
    22  CREATE INDEX bindex ON ab (b)
    23  
    24  query TTT
    25  EXECUTE change_index
    26  ----
    27  ·     distributed  true
    28  ·     vectorized   true
    29  scan  ·            ·
    30  ·     table        ab@bindex
    31  ·     spans        /10-/11
    32  
    33  statement ok
    34  DROP INDEX bindex
    35  
    36  query TTT
    37  EXECUTE change_index
    38  ----
    39  ·     distributed  true
    40  ·     vectorized   true
    41  scan  ·            ·
    42  ·     table        ab@primary
    43  ·     spans        FULL SCAN
    44  ·     filter       b = 10
    45  
    46  ## Statistics change: Create statistics and ensure that the plan is recalculated.
    47  statement ok
    48  CREATE TABLE cd (c INT PRIMARY KEY, d INT)
    49  
    50  statement ok
    51  PREPARE change_stats AS SELECT * FROM [EXPLAIN SELECT * FROM ab JOIN cd ON a=c]
    52  
    53  query TTT
    54  EXECUTE change_stats
    55  ----
    56  ·           distributed         true
    57  ·           vectorized          true
    58  merge-join  ·                   ·
    59   │          type                inner
    60   │          equality            (a) = (c)
    61   │          left cols are key   ·
    62   │          right cols are key  ·
    63   │          mergeJoinOrder      +"(a=c)"
    64   ├── scan   ·                   ·
    65   │          table               ab@primary
    66   │          spans               FULL SCAN
    67   └── scan   ·                   ·
    68  ·           table               cd@primary
    69  ·           spans               FULL SCAN
    70  
    71  statement ok
    72  CREATE STATISTICS s FROM ab
    73  
    74  # Now that the optimizer knows table ab has one row (and it assumes a much
    75  # higher number of rows for cd), it should choose lookup join.
    76  # We allow retry because stat cache invalidation happens asynchronously.
    77  query TTT retry
    78  EXECUTE change_stats
    79  ----
    80  ·            distributed            true
    81  ·            vectorized             true
    82  lookup-join  ·                      ·
    83   │           table                  cd@primary
    84   │           type                   inner
    85   │           equality               (a) = (c)
    86   │           equality cols are key  ·
    87   │           parallel               ·
    88   └── scan    ·                      ·
    89  ·            table                  ab@primary
    90  ·            spans                  FULL SCAN