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

     1  exec-ddl
     2  CREATE TABLE a
     3  (
     4      k INT PRIMARY KEY,
     5      i INT,
     6      f FLOAT,
     7      s STRING,
     8      j JSON,
     9      INDEX s_idx (s) STORING (i, f),
    10      INDEX si_idx (s DESC, i) STORING (j)
    11  )
    12  ----
    13  
    14  # ---------------------------------------------------
    15  # GenerateLimitedScans / PushLimitIntoConstrainedScan
    16  # ---------------------------------------------------
    17  
    18  opt
    19  SELECT * FROM a LIMIT 1
    20  ----
    21  scan a
    22   ├── columns: k:1!null i:2 f:3 s:4 j:5
    23   ├── limit: 1
    24   ├── key: ()
    25   └── fd: ()-->(1-5)
    26  
    27  # Combine limit with needed columns.
    28  opt
    29  SELECT s FROM a LIMIT 1
    30  ----
    31  scan a@s_idx
    32   ├── columns: s:4
    33   ├── limit: 1
    34   ├── key: ()
    35   └── fd: ()-->(4)
    36  
    37  # Combine limit with constraint.
    38  opt
    39  SELECT s FROM a WHERE s='foo' LIMIT 1
    40  ----
    41  scan a@s_idx
    42   ├── columns: s:4!null
    43   ├── constraint: /4/1: [/'foo' - /'foo']
    44   ├── limit: 1
    45   ├── key: ()
    46   └── fd: ()-->(4)
    47  
    48  # Limit of a limit.
    49  opt
    50  SELECT s FROM (SELECT s, i FROM a ORDER BY s LIMIT 10) a ORDER BY s, i LIMIT 1
    51  ----
    52  limit
    53   ├── columns: s:4  [hidden: i:2]
    54   ├── internal-ordering: +4,+2
    55   ├── cardinality: [0 - 1]
    56   ├── key: ()
    57   ├── fd: ()-->(2,4)
    58   ├── sort (segmented)
    59   │    ├── columns: i:2 s:4
    60   │    ├── cardinality: [0 - 10]
    61   │    ├── ordering: +4,+2
    62   │    ├── limit hint: 1.00
    63   │    └── scan a@s_idx
    64   │         ├── columns: i:2 s:4
    65   │         ├── limit: 10
    66   │         └── ordering: +4
    67   └── 1
    68  
    69  # Don't push when scan doesn't satisfy limit's ordering.
    70  opt
    71  SELECT s FROM a ORDER BY f LIMIT 1
    72  ----
    73  limit
    74   ├── columns: s:4  [hidden: f:3]
    75   ├── internal-ordering: +3
    76   ├── cardinality: [0 - 1]
    77   ├── key: ()
    78   ├── fd: ()-->(3,4)
    79   ├── sort
    80   │    ├── columns: f:3 s:4
    81   │    ├── ordering: +3
    82   │    ├── limit hint: 1.00
    83   │    └── scan a@s_idx
    84   │         └── columns: f:3 s:4
    85   └── 1
    86  
    87  # Don't push when limit is not a constant.
    88  opt
    89  SELECT s FROM a LIMIT (SELECT k FROM a LIMIT 1)
    90  ----
    91  limit
    92   ├── columns: s:4
    93   ├── immutable, side-effects
    94   ├── scan a@s_idx
    95   │    └── columns: s:4
    96   └── subquery
    97        └── scan a@s_idx
    98             ├── columns: k:6!null
    99             ├── limit: 1
   100             ├── key: ()
   101             └── fd: ()-->(6)
   102  
   103  memo
   104  SELECT s FROM a WHERE s='foo' LIMIT 1
   105  ----
   106  memo (optimized, ~7KB, required=[presentation: s:4])
   107   ├── G1: (limit G2 G3) (scan a@s_idx,cols=(4),constrained,lim=1) (scan a@si_idx,cols=(4),constrained,lim=1)
   108   │    └── [presentation: s:4]
   109   │         ├── best: (scan a@s_idx,cols=(4),constrained,lim=1)
   110   │         └── cost: 1.06
   111   ├── G2: (select G4 G5) (scan a@s_idx,cols=(4),constrained) (scan a@si_idx,cols=(4),constrained)
   112   │    └── [limit hint: 1.00]
   113   │         ├── best: (scan a@s_idx,cols=(4),constrained)
   114   │         └── cost: 2.11
   115   ├── G3: (const 1)
   116   ├── G4: (scan a,cols=(4)) (scan a@s_idx,cols=(4)) (scan a@si_idx,cols=(4))
   117   │    └── [limit hint: 100.00]
   118   │         ├── best: (scan a@s_idx,cols=(4))
   119   │         └── cost: 210.02
   120   ├── G5: (filters G6)
   121   ├── G6: (eq G7 G8)
   122   ├── G7: (variable s)
   123   └── G8: (const 'foo')
   124  
   125  # GenerateLimitedScans propagates row-level locking information.
   126  opt
   127  SELECT * FROM a LIMIT 1 FOR UPDATE
   128  ----
   129  scan a
   130   ├── columns: k:1!null i:2 f:3 s:4 j:5
   131   ├── limit: 1
   132   ├── locking: for-update
   133   ├── volatile, side-effects
   134   ├── key: ()
   135   └── fd: ()-->(1-5)
   136  
   137  # PushLimitIntoConstrainedScan propagates row-level locking information.
   138  opt
   139  SELECT s FROM a WHERE s='foo' LIMIT 1 FOR UPDATE
   140  ----
   141  scan a@s_idx
   142   ├── columns: s:4!null
   143   ├── constraint: /4/1: [/'foo' - /'foo']
   144   ├── limit: 1
   145   ├── locking: for-update
   146   ├── volatile, side-effects
   147   ├── key: ()
   148   └── fd: ()-->(4)
   149  
   150  # --------------------------------------------------
   151  # PushLimitIntoIndexJoin
   152  # --------------------------------------------------
   153  
   154  exec-ddl
   155  CREATE TABLE kuv (k INT PRIMARY KEY, u INT, v INT, INDEX (u))
   156  ----
   157  
   158  opt
   159  SELECT * FROM kuv ORDER BY u LIMIT 5
   160  ----
   161  index-join kuv
   162   ├── columns: k:1!null u:2 v:3
   163   ├── cardinality: [0 - 5]
   164   ├── key: (1)
   165   ├── fd: (1)-->(2,3)
   166   ├── ordering: +2
   167   └── scan kuv@secondary
   168        ├── columns: k:1!null u:2
   169        ├── limit: 5
   170        ├── key: (1)
   171        ├── fd: (1)-->(2)
   172        └── ordering: +2
   173  
   174  # Verify we don't push the limit if the ordering depends on a column not in the
   175  # input index.
   176  opt
   177  SELECT * FROM kuv WHERE u > 1 AND u < 10 ORDER BY u, v LIMIT 5
   178  ----
   179  limit
   180   ├── columns: k:1!null u:2!null v:3
   181   ├── internal-ordering: +2,+3
   182   ├── cardinality: [0 - 5]
   183   ├── key: (1)
   184   ├── fd: (1)-->(2,3)
   185   ├── ordering: +2,+3
   186   ├── sort (segmented)
   187   │    ├── columns: k:1!null u:2!null v:3
   188   │    ├── key: (1)
   189   │    ├── fd: (1)-->(2,3)
   190   │    ├── ordering: +2,+3
   191   │    ├── limit hint: 5.00
   192   │    └── index-join kuv
   193   │         ├── columns: k:1!null u:2!null v:3
   194   │         ├── key: (1)
   195   │         ├── fd: (1)-->(2,3)
   196   │         ├── ordering: +2
   197   │         └── scan kuv@secondary
   198   │              ├── columns: k:1!null u:2!null
   199   │              ├── constraint: /2/1: [/2 - /9]
   200   │              ├── key: (1)
   201   │              ├── fd: (1)-->(2)
   202   │              └── ordering: +2
   203   └── 5
   204  
   205  exec-ddl
   206  CREATE TABLE abcd (
   207    a INT PRIMARY KEY,
   208    b INT,
   209    c INT,
   210    d INT,
   211    INDEX b (b),
   212    INDEX cd (c,d),
   213    UNIQUE INDEX bcd (b,c,d)
   214  )
   215  ----
   216  
   217  opt
   218  EXPLAIN SELECT * FROM abcd@b WHERE a >= 20 AND a <= 30 ORDER BY b DESC LIMIT 5
   219  ----
   220  explain
   221   ├── columns: tree:5 field:6 description:7
   222   └── limit
   223        ├── columns: a:1!null b:2 c:3 d:4
   224        ├── internal-ordering: -2
   225        ├── cardinality: [0 - 5]
   226        ├── key: (1)
   227        ├── fd: (1)-->(2-4), (2-4)~~>(1)
   228        ├── ordering: -2
   229        ├── select
   230        │    ├── columns: a:1!null b:2 c:3 d:4
   231        │    ├── cardinality: [0 - 11]
   232        │    ├── key: (1)
   233        │    ├── fd: (1)-->(2-4), (2-4)~~>(1)
   234        │    ├── ordering: -2
   235        │    ├── limit hint: 5.00
   236        │    ├── index-join abcd
   237        │    │    ├── columns: a:1!null b:2 c:3 d:4
   238        │    │    ├── key: (1)
   239        │    │    ├── fd: (1)-->(2-4), (2-4)~~>(1)
   240        │    │    ├── ordering: -2
   241        │    │    ├── limit hint: 454.55
   242        │    │    └── scan abcd@b,rev
   243        │    │         ├── columns: a:1!null b:2
   244        │    │         ├── flags: force-index=b
   245        │    │         ├── key: (1)
   246        │    │         ├── fd: (1)-->(2)
   247        │    │         ├── ordering: -2
   248        │    │         └── limit hint: 454.55
   249        │    └── filters
   250        │         └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)]
   251        └── 5
   252  
   253  optsteps
   254  EXPLAIN SELECT * FROM abcd@b WHERE a >= 20 AND a <= 30 ORDER BY b DESC LIMIT 5
   255  ----
   256  ================================================================================
   257  Initial expression
   258    Cost: 10000000000000000159028911097599180468360808563945281389781327557747838772170381060813469985856815104.00
   259  ================================================================================
   260    explain
   261     ├── columns: tree:5 field:6 description:7
   262     └── sort
   263          ├── columns: a:1!null b:2 c:3 d:4
   264          ├── cardinality: [0 - 5]
   265          ├── key: (1)
   266          ├── fd: (1)-->(2-4), (2-4)~~>(1)
   267          ├── ordering: -2
   268          └── limit
   269               ├── columns: a:1!null b:2 c:3 d:4
   270               ├── internal-ordering: -2
   271               ├── cardinality: [0 - 5]
   272               ├── key: (1)
   273               ├── fd: (1)-->(2-4), (2-4)~~>(1)
   274               ├── sort
   275               │    ├── columns: a:1!null b:2 c:3 d:4
   276               │    ├── cardinality: [0 - 11]
   277               │    ├── key: (1)
   278               │    ├── fd: (1)-->(2-4), (2-4)~~>(1)
   279               │    ├── ordering: -2
   280               │    ├── limit hint: 5.00
   281               │    └── select
   282               │         ├── columns: a:1!null b:2 c:3 d:4
   283               │         ├── cardinality: [0 - 11]
   284               │         ├── key: (1)
   285               │         ├── fd: (1)-->(2-4), (2-4)~~>(1)
   286               │         ├── scan abcd
   287               │         │    ├── columns: a:1!null b:2 c:3 d:4
   288               │         │    ├── flags: force-index=b
   289               │         │    ├── key: (1)
   290               │         │    └── fd: (1)-->(2-4), (2-4)~~>(1)
   291               │         └── filters
   292               │              └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)]
   293               └── 5
   294  ================================================================================
   295  SimplifySelectFilters
   296    Cost: 10000000000000000159028911097599180468360808563945281389781327557747838772170381060813469985856815104.00
   297  ================================================================================
   298     explain
   299      ├── columns: tree:5 field:6 description:7
   300      └── sort
   301           ├── columns: a:1!null b:2 c:3 d:4
   302           ├── cardinality: [0 - 5]
   303           ├── key: (1)
   304           ├── fd: (1)-->(2-4), (2-4)~~>(1)
   305           ├── ordering: -2
   306           └── limit
   307                ├── columns: a:1!null b:2 c:3 d:4
   308                ├── internal-ordering: -2
   309                ├── cardinality: [0 - 5]
   310                ├── key: (1)
   311                ├── fd: (1)-->(2-4), (2-4)~~>(1)
   312                ├── sort
   313                │    ├── columns: a:1!null b:2 c:3 d:4
   314    -           │    ├── cardinality: [0 - 11]
   315                │    ├── key: (1)
   316                │    ├── fd: (1)-->(2-4), (2-4)~~>(1)
   317                │    ├── ordering: -2
   318                │    ├── limit hint: 5.00
   319                │    └── select
   320                │         ├── columns: a:1!null b:2 c:3 d:4
   321    -           │         ├── cardinality: [0 - 11]
   322                │         ├── key: (1)
   323                │         ├── fd: (1)-->(2-4), (2-4)~~>(1)
   324                │         ├── scan abcd
   325                │         │    ├── columns: a:1!null b:2 c:3 d:4
   326                │         │    ├── flags: force-index=b
   327                │         │    ├── key: (1)
   328                │         │    └── fd: (1)-->(2-4), (2-4)~~>(1)
   329                │         └── filters
   330    -           │              └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)]
   331    +           │              ├── a:1 >= 20 [outer=(1), constraints=(/1: [/20 - ]; tight)]
   332    +           │              └── a:1 <= 30 [outer=(1), constraints=(/1: (/NULL - /30]; tight)]
   333                └── 5
   334  ================================================================================
   335  ConsolidateSelectFilters
   336    Cost: 10000000000000000159028911097599180468360808563945281389781327557747838772170381060813469985856815104.00
   337  ================================================================================
   338     explain
   339      ├── columns: tree:5 field:6 description:7
   340      └── sort
   341           ├── columns: a:1!null b:2 c:3 d:4
   342           ├── cardinality: [0 - 5]
   343           ├── key: (1)
   344           ├── fd: (1)-->(2-4), (2-4)~~>(1)
   345           ├── ordering: -2
   346           └── limit
   347                ├── columns: a:1!null b:2 c:3 d:4
   348                ├── internal-ordering: -2
   349                ├── cardinality: [0 - 5]
   350                ├── key: (1)
   351                ├── fd: (1)-->(2-4), (2-4)~~>(1)
   352                ├── sort
   353                │    ├── columns: a:1!null b:2 c:3 d:4
   354    +           │    ├── cardinality: [0 - 11]
   355                │    ├── key: (1)
   356                │    ├── fd: (1)-->(2-4), (2-4)~~>(1)
   357                │    ├── ordering: -2
   358                │    ├── limit hint: 5.00
   359                │    └── select
   360                │         ├── columns: a:1!null b:2 c:3 d:4
   361    +           │         ├── cardinality: [0 - 11]
   362                │         ├── key: (1)
   363                │         ├── fd: (1)-->(2-4), (2-4)~~>(1)
   364                │         ├── scan abcd
   365                │         │    ├── columns: a:1!null b:2 c:3 d:4
   366                │         │    ├── flags: force-index=b
   367                │         │    ├── key: (1)
   368                │         │    └── fd: (1)-->(2-4), (2-4)~~>(1)
   369                │         └── filters
   370    -           │              ├── a:1 >= 20 [outer=(1), constraints=(/1: [/20 - ]; tight)]
   371    -           │              └── a:1 <= 30 [outer=(1), constraints=(/1: (/NULL - /30]; tight)]
   372    +           │              └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)]
   373                └── 5
   374  ================================================================================
   375  GenerateIndexScans
   376    Cost: 5134.91
   377  ================================================================================
   378     explain
   379      ├── columns: tree:5 field:6 description:7
   380    - └── sort
   381    + └── limit
   382           ├── columns: a:1!null b:2 c:3 d:4
   383    +      ├── internal-ordering: -2
   384           ├── cardinality: [0 - 5]
   385           ├── key: (1)
   386           ├── fd: (1)-->(2-4), (2-4)~~>(1)
   387           ├── ordering: -2
   388    -      └── limit
   389    -           ├── columns: a:1!null b:2 c:3 d:4
   390    -           ├── internal-ordering: -2
   391    -           ├── cardinality: [0 - 5]
   392    -           ├── key: (1)
   393    -           ├── fd: (1)-->(2-4), (2-4)~~>(1)
   394    -           ├── sort
   395    -           │    ├── columns: a:1!null b:2 c:3 d:4
   396    -           │    ├── cardinality: [0 - 11]
   397    -           │    ├── key: (1)
   398    -           │    ├── fd: (1)-->(2-4), (2-4)~~>(1)
   399    -           │    ├── ordering: -2
   400    -           │    ├── limit hint: 5.00
   401    -           │    └── select
   402    -           │         ├── columns: a:1!null b:2 c:3 d:4
   403    -           │         ├── cardinality: [0 - 11]
   404    -           │         ├── key: (1)
   405    -           │         ├── fd: (1)-->(2-4), (2-4)~~>(1)
   406    -           │         ├── scan abcd
   407    -           │         │    ├── columns: a:1!null b:2 c:3 d:4
   408    -           │         │    ├── flags: force-index=b
   409    -           │         │    ├── key: (1)
   410    -           │         │    └── fd: (1)-->(2-4), (2-4)~~>(1)
   411    -           │         └── filters
   412    -           │              └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)]
   413    -           └── 5
   414    +      ├── select
   415    +      │    ├── columns: a:1!null b:2 c:3 d:4
   416    +      │    ├── cardinality: [0 - 11]
   417    +      │    ├── key: (1)
   418    +      │    ├── fd: (1)-->(2-4), (2-4)~~>(1)
   419    +      │    ├── ordering: -2
   420    +      │    ├── limit hint: 5.00
   421    +      │    ├── index-join abcd
   422    +      │    │    ├── columns: a:1!null b:2 c:3 d:4
   423    +      │    │    ├── key: (1)
   424    +      │    │    ├── fd: (1)-->(2-4), (2-4)~~>(1)
   425    +      │    │    ├── ordering: -2
   426    +      │    │    ├── limit hint: 454.55
   427    +      │    │    └── scan abcd@b,rev
   428    +      │    │         ├── columns: a:1!null b:2
   429    +      │    │         ├── flags: force-index=b
   430    +      │    │         ├── key: (1)
   431    +      │    │         ├── fd: (1)-->(2)
   432    +      │    │         ├── ordering: -2
   433    +      │    │         └── limit hint: 454.55
   434    +      │    └── filters
   435    +      │         └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)]
   436    +      └── 5
   437  --------------------------------------------------------------------------------
   438  GenerateZigzagJoins (no changes)
   439  --------------------------------------------------------------------------------
   440  --------------------------------------------------------------------------------
   441  GenerateConstrainedScans (no changes)
   442  --------------------------------------------------------------------------------
   443  ================================================================================
   444  Final best expression
   445    Cost: 5134.91
   446  ================================================================================
   447    explain
   448     ├── columns: tree:5 field:6 description:7
   449     └── limit
   450          ├── columns: a:1!null b:2 c:3 d:4
   451          ├── internal-ordering: -2
   452          ├── cardinality: [0 - 5]
   453          ├── key: (1)
   454          ├── fd: (1)-->(2-4), (2-4)~~>(1)
   455          ├── ordering: -2
   456          ├── select
   457          │    ├── columns: a:1!null b:2 c:3 d:4
   458          │    ├── cardinality: [0 - 11]
   459          │    ├── key: (1)
   460          │    ├── fd: (1)-->(2-4), (2-4)~~>(1)
   461          │    ├── ordering: -2
   462          │    ├── limit hint: 5.00
   463          │    ├── index-join abcd
   464          │    │    ├── columns: a:1!null b:2 c:3 d:4
   465          │    │    ├── key: (1)
   466          │    │    ├── fd: (1)-->(2-4), (2-4)~~>(1)
   467          │    │    ├── ordering: -2
   468          │    │    ├── limit hint: 454.55
   469          │    │    └── scan abcd@b,rev
   470          │    │         ├── columns: a:1!null b:2
   471          │    │         ├── flags: force-index=b
   472          │    │         ├── key: (1)
   473          │    │         ├── fd: (1)-->(2)
   474          │    │         ├── ordering: -2
   475          │    │         └── limit hint: 454.55
   476          │    └── filters
   477          │         └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)]
   478          └── 5
   479  
   480  # --------------------------------------------------
   481  # PushLimitIntoOffset + GenerateLimitedScans
   482  # --------------------------------------------------
   483  
   484  # Regression testing for #30416.
   485  # The limit is pushed down the offset and so an appropriate index scan is used
   486  # over a primary key scan.
   487  opt
   488  SELECT * from a ORDER BY s LIMIT 10 OFFSET 10
   489  ----
   490  offset
   491   ├── columns: k:1!null i:2 f:3 s:4 j:5
   492   ├── internal-ordering: +4
   493   ├── cardinality: [0 - 10]
   494   ├── key: (1)
   495   ├── fd: (1)-->(2-5)
   496   ├── ordering: +4
   497   ├── index-join a
   498   │    ├── columns: k:1!null i:2 f:3 s:4 j:5
   499   │    ├── cardinality: [0 - 20]
   500   │    ├── key: (1)
   501   │    ├── fd: (1)-->(2-5)
   502   │    ├── ordering: +4
   503   │    └── scan a@s_idx
   504   │         ├── columns: k:1!null i:2 f:3 s:4
   505   │         ├── limit: 20
   506   │         ├── key: (1)
   507   │         ├── fd: (1)-->(2-4)
   508   │         └── ordering: +4
   509   └── 10
   510  
   511  # The right index is used for the limited scan based on the order.
   512  opt
   513  SELECT * from a ORDER BY s DESC LIMIT 10 OFFSET 10
   514  ----
   515  offset
   516   ├── columns: k:1!null i:2 f:3 s:4 j:5
   517   ├── internal-ordering: -4
   518   ├── cardinality: [0 - 10]
   519   ├── key: (1)
   520   ├── fd: (1)-->(2-5)
   521   ├── ordering: -4
   522   ├── index-join a
   523   │    ├── columns: k:1!null i:2 f:3 s:4 j:5
   524   │    ├── cardinality: [0 - 20]
   525   │    ├── key: (1)
   526   │    ├── fd: (1)-->(2-5)
   527   │    ├── ordering: -4
   528   │    └── scan a@si_idx
   529   │         ├── columns: k:1!null i:2 s:4 j:5
   530   │         ├── limit: 20
   531   │         ├── key: (1)
   532   │         ├── fd: (1)-->(2,4,5)
   533   │         └── ordering: -4
   534   └── 10
   535  
   536  # PushLimitIntoIndexJoin propagates row-level locking information.
   537  opt
   538  SELECT * FROM kuv ORDER BY u LIMIT 5 FOR UPDATE
   539  ----
   540  index-join kuv
   541   ├── columns: k:1!null u:2 v:3
   542   ├── cardinality: [0 - 5]
   543   ├── volatile, side-effects
   544   ├── key: (1)
   545   ├── fd: (1)-->(2,3)
   546   ├── ordering: +2
   547   └── scan kuv@secondary
   548        ├── columns: k:1!null u:2
   549        ├── limit: 5
   550        ├── locking: for-update
   551        ├── volatile, side-effects
   552        ├── key: (1)
   553        ├── fd: (1)-->(2)
   554        └── ordering: +2