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

     1  # This file tests that the OptTester flag perturb-cost works. It's not possible
     2  # to include tests with the opt directive (other than for trivial scalar
     3  # queries), since by construction those tests will produce a random query plan
     4  # and we cannot predict the output in advance. For example,
     5  #   `SELECT * FROM a JOIN b ON a.x=b.x`
     6  # may produce one of at least 6 different plans.
     7  
     8  # For this reason, the perturb-cost flag is only intended for debugging at this
     9  # time, by using the "-rewrite=true" flag.
    10  
    11  exec-ddl
    12  CREATE TABLE a (x INT PRIMARY KEY, y INT)
    13  ----
    14  
    15  exec-ddl
    16  CREATE TABLE b (x INT PRIMARY KEY)
    17  ----
    18  
    19  norm perturb-cost=(0.5)
    20  SELECT * FROM a JOIN b ON a.x=b.x ORDER BY a.y
    21  ----
    22  sort
    23   ├── columns: x:1!null y:2 x:3!null
    24   ├── stats: [rows=1000, distinct(1)=1000, null(1)=0, distinct(3)=1000, null(3)=0]
    25   ├── cost: 2319.37569
    26   ├── key: (3)
    27   ├── fd: (1)-->(2), (1)==(3), (3)==(1)
    28   ├── ordering: +2
    29   └── inner-join (hash)
    30        ├── columns: a.x:1!null y:2 b.x:3!null
    31        ├── stats: [rows=1000, distinct(1)=1000, null(1)=0, distinct(3)=1000, null(3)=0]
    32        ├── cost: 2100.05
    33        ├── key: (3)
    34        ├── fd: (1)-->(2), (1)==(3), (3)==(1)
    35        ├── scan a
    36        │    ├── columns: a.x:1!null y:2
    37        │    ├── stats: [rows=1000, distinct(1)=1000, null(1)=0]
    38        │    ├── cost: 1040.02
    39        │    ├── key: (1)
    40        │    └── fd: (1)-->(2)
    41        ├── scan b
    42        │    ├── columns: b.x:3!null
    43        │    ├── stats: [rows=1000, distinct(3)=1000, null(3)=0]
    44        │    ├── cost: 1020.02
    45        │    └── key: (3)
    46        └── filters
    47             └── a.x:1 = b.x:3 [outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ]), fd=(1)==(3), (3)==(1)]
    48  
    49  opt perturb-cost=(0.9)
    50  SELECT 1
    51  ----
    52  values
    53   ├── columns: "?column?":1!null
    54   ├── cardinality: [1 - 1]
    55   ├── stats: [rows=1]
    56   ├── cost: 0.02
    57   ├── key: ()
    58   ├── fd: ()-->(1)
    59   └── (1,)
    60  
    61  opt perturb-cost=(2.5)
    62  SELECT 1
    63  ----
    64  values
    65   ├── columns: "?column?":1!null
    66   ├── cardinality: [1 - 1]
    67   ├── stats: [rows=1]
    68   ├── cost: 0.02
    69   ├── key: ()
    70   ├── fd: ()-->(1)
    71   └── (1,)