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

     1  # --------------------------------------------------
     2  # GenerateConstrainedScans + Partitioning
     3  # --------------------------------------------------
     4  
     5  # Create a truly distributed table. Partitioned appropriately.
     6  exec-ddl
     7  CREATE TABLE partitioned (
     8      planet STRING, region STRING, subregion STRING, val INT8,
     9      PRIMARY KEY (planet, region, subregion, val)
    10  )
    11      PARTITION BY LIST (planet, region, subregion)
    12          (
    13              PARTITION westcoast VALUES IN (('earth', 'us', 'seattle'), ('earth', 'us', 'cali')),
    14              PARTITION eu VALUES IN (('earth', 'eu', DEFAULT)),
    15              PARTITION us VALUES IN (('earth', 'us', DEFAULT)),
    16              PARTITION earth VALUES IN (('earth', DEFAULT)),
    17              PARTITION mars VALUES IN (('mars', DEFAULT)),
    18              PARTITION titan VALUES IN (('jupiter', 'titan', DEFAULT)),
    19              PARTITION red_spot VALUES IN (('jupiter', 'titan', 'red spot')),
    20              PARTITION jupiter VALUES IN (('jupiter', DEFAULT)),
    21              PARTITION default VALUES IN (DEFAULT)
    22          )
    23  ----
    24  
    25  # Use partition values to constrain the scan.
    26  opt
    27  EXPLAIN (OPT, VERBOSE)
    28    SELECT
    29    *
    30    FROM
    31    partitioned
    32    WHERE
    33    val = 1
    34  ----
    35  explain
    36   ├── columns: text:5
    37   ├── mode: opt, verbose
    38   └── select
    39        ├── columns: planet:1!null region:2!null subregion:3!null val:4!null
    40        ├── key: (1-3)
    41        ├── fd: ()-->(4)
    42        ├── scan partitioned
    43        │    ├── columns: planet:1!null region:2!null subregion:3!null val:4!null
    44        │    ├── constraint: /1/2/3/4
    45        │    │    ├── [ - /'earth'/'us'/'cali')
    46        │    │    ├── [/'earth'/'us'/'cali'/1 - /'earth'/'us'/'cali'/1]
    47        │    │    ├── [/'earth'/'us'/e'cali\x00'/1 - /'earth'/'us'/'seattle')
    48        │    │    ├── [/'earth'/'us'/'seattle'/1 - /'earth'/'us'/'seattle'/1]
    49        │    │    ├── [/'earth'/'us'/e'seattle\x00'/1 - /'jupiter'/'titan'/'red spot')
    50        │    │    ├── [/'jupiter'/'titan'/'red spot'/1 - /'jupiter'/'titan'/'red spot'/1]
    51        │    │    └── [/'jupiter'/'titan'/e'red spot\x00'/1 - ]
    52        │    └── key: (1-4)
    53        └── filters
    54             └── val:4 = 1 [outer=(4), constraints=(/4: [/1 - /1]; tight), fd=()-->(4)]
    55  
    56  # Regression test for #42147.
    57  exec-ddl
    58  CREATE TABLE tab42147 (
    59    id INT8 NOT NULL,
    60    locality STRING,
    61    CONSTRAINT pk PRIMARY KEY (locality ASC, id ASC),
    62    CONSTRAINT id UNIQUE (id ASC)
    63  )
    64    PARTITION BY LIST (locality)
    65      (
    66        PARTITION uswest VALUES IN ('us-west'),
    67        PARTITION uscentral VALUES IN ('us-central'),
    68        PARTITION asiasoutheast VALUES IN ('asia-southeast')
    69      )
    70  ----
    71  
    72  opt
    73  SELECT id FROM tab42147 WHERE id = 1
    74  ----
    75  scan tab42147@id
    76   ├── columns: id:1!null
    77   ├── constraint: /1: [/1 - /1]
    78   ├── cardinality: [0 - 1]
    79   ├── key: ()
    80   └── fd: ()-->(1)