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

     1  exec-ddl
     2  CREATE TABLE a (k INT PRIMARY KEY, i INT, s STRING, d DECIMAL NOT NULL)
     3  ----
     4  
     5  opt
     6  SELECT k, s FROM a
     7  ----
     8  scan a
     9   ├── columns: k:1!null s:3
    10   ├── stats: [rows=1000]
    11   ├── cost: 1060.02
    12   ├── key: (1)
    13   └── fd: (1)-->(3)
    14  
    15  exec-ddl
    16  ALTER TABLE a INJECT STATISTICS '[
    17    {
    18      "columns": ["k"],
    19      "created_at": "2019-02-08 04:10:40.001179+00:00",
    20      "row_count": 100000,
    21      "distinct_count": 100000
    22    },
    23    {
    24      "columns": ["i"],
    25      "created_at": "2019-02-08 04:10:40.001179+00:00",
    26      "row_count": 100000,
    27      "distinct_count": 100
    28    }
    29  ]'
    30  ----
    31  
    32  # The limit hint is propagated to the scan and the cost is slightly more than
    33  # the limit hint * 2 (the scan soft limit multiplier).
    34  opt
    35  SELECT * FROM a WHERE k > 5 AND i IN (1, 3, 5, 7, 9) LIMIT 20
    36  ----
    37  limit
    38   ├── columns: k:1!null i:2!null s:3 d:4!null
    39   ├── cardinality: [0 - 20]
    40   ├── stats: [rows=20]
    41   ├── cost: 642.134762
    42   ├── key: (1)
    43   ├── fd: (1)-->(2-4)
    44   ├── select
    45   │    ├── columns: k:1!null i:2!null s:3 d:4!null
    46   │    ├── stats: [rows=4666.66667, distinct(1)=4666.66667, null(1)=0, distinct(2)=5, null(2)=0, distinct(1,2)=4666.66667, null(1,2)=0]
    47   │    ├── cost: 641.924762
    48   │    ├── key: (1)
    49   │    ├── fd: (1)-->(2-4)
    50   │    ├── limit hint: 20.00
    51   │    ├── scan a
    52   │    │    ├── columns: k:1!null i:2 s:3 d:4!null
    53   │    │    ├── constraint: /1: [/6 - ]
    54   │    │    ├── stats: [rows=33333.3333, distinct(1)=33333.3333, null(1)=0]
    55   │    │    ├── cost: 308.581429
    56   │    │    ├── key: (1)
    57   │    │    ├── fd: (1)-->(2-4)
    58   │    │    └── limit hint: 142.86
    59   │    └── filters
    60   │         └── i:2 IN (1, 3, 5, 7, 9) [outer=(2), constraints=(/2: [/1 - /1] [/3 - /3] [/5 - /5] [/7 - /7] [/9 - /9]; tight)]
    61   └── 20
    62  
    63  # The limit hint is propagated, but the cost is not multiplied by 2 (the scan
    64  # soft limit multiplier) since the row count is known to be less than 400 * 2.
    65  opt
    66  SELECT * FROM a WHERE k > 0 AND k <= 450 AND i IN (1, 3, 5, 7, 9) LIMIT 20
    67  ----
    68  limit
    69   ├── columns: k:1!null i:2!null s:3 d:4!null
    70   ├── cardinality: [0 - 20]
    71   ├── stats: [rows=20]
    72   ├── cost: 52.4648066
    73   ├── key: (1)
    74   ├── fd: (1)-->(2-4)
    75   ├── select
    76   │    ├── columns: k:1!null i:2!null s:3 d:4!null
    77   │    ├── cardinality: [0 - 450]
    78   │    ├── stats: [rows=407.25, distinct(1)=407.25, null(1)=0, distinct(2)=5, null(2)=0, distinct(1,2)=407.25, null(1,2)=0]
    79   │    ├── cost: 52.2548066
    80   │    ├── key: (1)
    81   │    ├── fd: (1)-->(2-4)
    82   │    ├── limit hint: 20.00
    83   │    ├── scan a
    84   │    │    ├── columns: k:1!null i:2 s:3 d:4!null
    85   │    │    ├── constraint: /1: [/1 - /450]
    86   │    │    ├── cardinality: [0 - 450]
    87   │    │    ├── stats: [rows=450, distinct(1)=450, null(1)=0]
    88   │    │    ├── cost: 47.7448066
    89   │    │    ├── key: (1)
    90   │    │    ├── fd: (1)-->(2-4)
    91   │    │    └── limit hint: 22.10
    92   │    └── filters
    93   │         └── i:2 IN (1, 3, 5, 7, 9) [outer=(2), constraints=(/2: [/1 - /1] [/3 - /3] [/5 - /5] [/7 - /7] [/9 - /9]; tight)]
    94   └── 20
    95  
    96  # Regression test for #35042. Ensure we always prefer constrained scans.
    97  exec-ddl
    98  CREATE TABLE speed_test (id INT PRIMARY KEY DEFAULT unique_rowid())
    99  ----
   100  
   101  opt
   102  SELECT id FROM speed_test@primary WHERE id BETWEEN 1 AND 1000 AND ((id % 16) = 0)
   103  ----
   104  select
   105   ├── columns: id:1!null
   106   ├── cardinality: [0 - 1000]
   107   ├── stats: [rows=333.333333, distinct(1)=333.333333, null(1)=0]
   108   ├── cost: 1030.02
   109   ├── key: (1)
   110   ├── scan speed_test
   111   │    ├── columns: id:1!null
   112   │    ├── constraint: /1: [/1 - /1000]
   113   │    ├── flags: force-index=primary
   114   │    ├── cardinality: [0 - 1000]
   115   │    ├── stats: [rows=1000, distinct(1)=1000, null(1)=0]
   116   │    ├── cost: 1020.01
   117   │    └── key: (1)
   118   └── filters
   119        └── (id:1 % 16) = 0 [outer=(1)]
   120  
   121  opt
   122  SELECT id FROM speed_test@primary WHERE id BETWEEN 1 AND 2000 AND ((id % 16) = 0)
   123  ----
   124  select
   125   ├── columns: id:1!null
   126   ├── cardinality: [0 - 2000]
   127   ├── stats: [rows=333.333333, distinct(1)=333.333333, null(1)=0]
   128   ├── cost: 1030.02
   129   ├── key: (1)
   130   ├── scan speed_test
   131   │    ├── columns: id:1!null
   132   │    ├── constraint: /1: [/1 - /2000]
   133   │    ├── flags: force-index=primary
   134   │    ├── cardinality: [0 - 2000]
   135   │    ├── stats: [rows=1000, distinct(1)=1000, null(1)=0]
   136   │    ├── cost: 1020.01
   137   │    └── key: (1)
   138   └── filters
   139        └── (id:1 % 16) = 0 [outer=(1)]