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

     1  exec-ddl
     2  CREATE TABLE a (k INT PRIMARY KEY, i INT, s STRING, d DECIMAL NOT NULL)
     3  ----
     4  
     5  exec-ddl
     6  CREATE TABLE b (x INT, z INT NOT NULL)
     7  ----
     8  
     9  opt
    10  SELECT k, x FROM a INNER JOIN b ON k=x WHERE d=1.0
    11  ----
    12  project
    13   ├── columns: k:1!null x:5!null
    14   ├── stats: [rows=99]
    15   ├── cost: 2124.725
    16   ├── fd: (1)==(5), (5)==(1)
    17   └── inner-join (hash)
    18        ├── columns: k:1!null d:4!null x:5!null
    19        ├── stats: [rows=99, distinct(1)=10, null(1)=0, distinct(5)=10, null(5)=0]
    20        ├── cost: 2123.725
    21        ├── fd: ()-->(4), (1)==(5), (5)==(1)
    22        ├── scan b
    23        │    ├── columns: x:5
    24        │    ├── stats: [rows=1000, distinct(5)=100, null(5)=10]
    25        │    └── cost: 1040.02
    26        ├── select
    27        │    ├── columns: k:1!null d:4!null
    28        │    ├── stats: [rows=10, distinct(1)=10, null(1)=0, distinct(4)=1, null(4)=0]
    29        │    ├── cost: 1070.03
    30        │    ├── key: (1)
    31        │    ├── fd: ()-->(4)
    32        │    ├── scan a
    33        │    │    ├── columns: k:1!null d:4!null
    34        │    │    ├── stats: [rows=1000, distinct(1)=1000, null(1)=0, distinct(4)=100, null(4)=0]
    35        │    │    ├── cost: 1060.02
    36        │    │    ├── key: (1)
    37        │    │    └── fd: (1)-->(4)
    38        │    └── filters
    39        │         └── d:4 = 1.0 [outer=(4), constraints=(/4: [/1.0 - /1.0]; tight), fd=()-->(4)]
    40        └── filters
    41             └── k:1 = x:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
    42  
    43  # Verify that we pick merge join if we force it.
    44  opt
    45  SELECT k, x FROM a INNER MERGE JOIN b ON k=x
    46  ----
    47  inner-join (merge)
    48   ├── columns: k:1!null x:5!null
    49   ├── flags: force merge join
    50   ├── left ordering: +1
    51   ├── right ordering: +5
    52   ├── stats: [rows=990, distinct(1)=99, null(1)=0, distinct(5)=99, null(5)=0]
    53   ├── cost: 2339.27569
    54   ├── fd: (1)==(5), (5)==(1)
    55   ├── scan a
    56   │    ├── columns: k:1!null
    57   │    ├── stats: [rows=1000, distinct(1)=1000, null(1)=0]
    58   │    ├── cost: 1050.02
    59   │    ├── key: (1)
    60   │    └── ordering: +1
    61   ├── sort
    62   │    ├── columns: x:5
    63   │    ├── stats: [rows=1000, distinct(5)=100, null(5)=10]
    64   │    ├── cost: 1259.34569
    65   │    ├── ordering: +5
    66   │    └── scan b
    67   │         ├── columns: x:5
    68   │         ├── stats: [rows=1000, distinct(5)=100, null(5)=10]
    69   │         └── cost: 1040.02
    70   └── filters (true)
    71  
    72  # Verify that we pick lookup join if we force it. Note that lookup join is only
    73  # possible if b is the left table.
    74  opt
    75  SELECT k, x FROM b INNER LOOKUP JOIN a ON k=x
    76  ----
    77  inner-join (lookup a)
    78   ├── columns: k:4!null x:1!null
    79   ├── flags: force lookup join (into right side)
    80   ├── key columns: [1] = [4]
    81   ├── lookup columns are key
    82   ├── stats: [rows=990, distinct(1)=99, null(1)=0, distinct(4)=99, null(4)=0]
    83   ├── cost: 7069.53
    84   ├── fd: (1)==(4), (4)==(1)
    85   ├── scan b
    86   │    ├── columns: x:1
    87   │    ├── stats: [rows=1000, distinct(1)=100, null(1)=10]
    88   │    └── cost: 1040.02
    89   └── filters (true)
    90  
    91  
    92  # Verify that if we force lookup join but one isn't possible, the hash join has
    93  # huge cost (this will result in an error if we try to execbuild the result).
    94  opt
    95  SELECT k, x FROM a INNER LOOKUP JOIN b ON k=x
    96  ----
    97  inner-join (hash)
    98   ├── columns: k:1!null x:5!null
    99   ├── flags: force lookup join (into right side)
   100   ├── stats: [rows=990, distinct(1)=99, null(1)=0, distinct(5)=99, null(5)=0]
   101   ├── cost: 1e+100
   102   ├── fd: (1)==(5), (5)==(1)
   103   ├── scan a
   104   │    ├── columns: k:1!null
   105   │    ├── stats: [rows=1000, distinct(1)=1000, null(1)=0]
   106   │    ├── cost: 1050.02
   107   │    └── key: (1)
   108   ├── scan b
   109   │    ├── columns: x:5
   110   │    ├── stats: [rows=1000, distinct(5)=100, null(5)=10]
   111   │    └── cost: 1040.02
   112   └── filters
   113        └── k:1 = x:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
   114  
   115  exec-ddl
   116  ALTER TABLE a INJECT STATISTICS '[
   117    {
   118      "columns": ["k"],
   119      "created_at": "2019-02-08 04:10:40.001179+00:00",
   120      "row_count": 100000,
   121      "distinct_count": 100000
   122    }
   123  ]'
   124  ----
   125  
   126  exec-ddl
   127  ALTER TABLE b INJECT STATISTICS '[
   128    {
   129      "columns": ["x"],
   130      "created_at": "2019-02-08 04:10:40.001179+00:00",
   131      "row_count": 10000,
   132      "distinct_count": 1000
   133    }
   134  ]'
   135  ----
   136  
   137  # Lookup join with no limit hint.
   138  opt
   139  SELECT * FROM a JOIN b ON k=z WHERE x > 0 AND x <= 5000
   140  ----
   141  inner-join (lookup a)
   142   ├── columns: k:1!null i:2 s:3 d:4!null x:5!null z:6!null
   143   ├── key columns: [6] = [1]
   144   ├── lookup columns are key
   145   ├── stats: [rows=10000, distinct(1)=1000, null(1)=0, distinct(6)=1000, null(6)=0]
   146   ├── cost: 71400.04
   147   ├── fd: (1)-->(2-4), (1)==(6), (6)==(1)
   148   ├── select
   149   │    ├── columns: x:5!null z:6!null
   150   │    ├── stats: [rows=10000, distinct(5)=1000, null(5)=0, distinct(6)=1000, null(6)=0]
   151   │    ├── cost: 10600.03
   152   │    ├── scan b
   153   │    │    ├── columns: x:5 z:6!null
   154   │    │    ├── stats: [rows=10000, distinct(5)=1000, null(5)=0, distinct(6)=1000, null(6)=0]
   155   │    │    └── cost: 10500.02
   156   │    └── filters
   157   │         └── (x:5 > 0) AND (x:5 <= 5000) [outer=(5), constraints=(/5: [/1 - /5000]; tight)]
   158   └── filters (true)
   159  
   160  # With the limit hint, the cost of the lookup join is reduced.
   161  opt
   162  SELECT * FROM a JOIN b ON k=z WHERE x > 0 AND x <= 5000 LIMIT 6000
   163  ----
   164  limit
   165   ├── columns: k:1!null i:2 s:3 d:4!null x:5!null z:6!null
   166   ├── cardinality: [0 - 6000]
   167   ├── stats: [rows=6000]
   168   ├── cost: 47140.05
   169   ├── fd: (1)-->(2-4), (1)==(6), (6)==(1)
   170   ├── inner-join (lookup a)
   171   │    ├── columns: k:1!null i:2 s:3 d:4!null x:5!null z:6!null
   172   │    ├── key columns: [6] = [1]
   173   │    ├── lookup columns are key
   174   │    ├── stats: [rows=10000, distinct(1)=1000, null(1)=0, distinct(6)=1000, null(6)=0]
   175   │    ├── cost: 47080.04
   176   │    ├── fd: (1)-->(2-4), (1)==(6), (6)==(1)
   177   │    ├── limit hint: 6000.00
   178   │    ├── select
   179   │    │    ├── columns: x:5!null z:6!null
   180   │    │    ├── stats: [rows=10000, distinct(5)=1000, null(5)=0, distinct(6)=1000, null(6)=0]
   181   │    │    ├── cost: 10600.03
   182   │    │    ├── limit hint: 6000.00
   183   │    │    ├── scan b
   184   │    │    │    ├── columns: x:5 z:6!null
   185   │    │    │    ├── stats: [rows=10000, distinct(5)=1000, null(5)=0, distinct(6)=1000, null(6)=0]
   186   │    │    │    ├── cost: 10500.02
   187   │    │    │    └── limit hint: 6000.00
   188   │    │    └── filters
   189   │    │         └── (x:5 > 0) AND (x:5 <= 5000) [outer=(5), constraints=(/5: [/1 - /5000]; tight)]
   190   │    └── filters (true)
   191   └── 6000
   192  
   193  # The limit hint for the lookup join input will be rounded up to the nearest
   194  # multiple of the batch size, so the cost of the lookup join here is the same
   195  # as the test case above.
   196  opt
   197  SELECT * FROM a JOIN b ON k=z WHERE x > 0 AND x <= 5000 LIMIT 5950
   198  ----
   199  limit
   200   ├── columns: k:1!null i:2 s:3 d:4!null x:5!null z:6!null
   201   ├── cardinality: [0 - 5950]
   202   ├── stats: [rows=5950]
   203   ├── cost: 47139.55
   204   ├── fd: (1)-->(2-4), (1)==(6), (6)==(1)
   205   ├── inner-join (lookup a)
   206   │    ├── columns: k:1!null i:2 s:3 d:4!null x:5!null z:6!null
   207   │    ├── key columns: [6] = [1]
   208   │    ├── lookup columns are key
   209   │    ├── stats: [rows=10000, distinct(1)=1000, null(1)=0, distinct(6)=1000, null(6)=0]
   210   │    ├── cost: 47080.04
   211   │    ├── fd: (1)-->(2-4), (1)==(6), (6)==(1)
   212   │    ├── limit hint: 5950.00
   213   │    ├── select
   214   │    │    ├── columns: x:5!null z:6!null
   215   │    │    ├── stats: [rows=10000, distinct(5)=1000, null(5)=0, distinct(6)=1000, null(6)=0]
   216   │    │    ├── cost: 10600.03
   217   │    │    ├── limit hint: 6000.00
   218   │    │    ├── scan b
   219   │    │    │    ├── columns: x:5 z:6!null
   220   │    │    │    ├── stats: [rows=10000, distinct(5)=1000, null(5)=0, distinct(6)=1000, null(6)=0]
   221   │    │    │    ├── cost: 10500.02
   222   │    │    │    └── limit hint: 6000.00
   223   │    │    └── filters
   224   │    │         └── (x:5 > 0) AND (x:5 <= 5000) [outer=(5), constraints=(/5: [/1 - /5000]; tight)]
   225   │    └── filters (true)
   226   └── 5950
   227  
   228  # Test case where the best plan is a lookup join only if the rows processed are
   229  # also scaled correctly according to the limit hint (#48791).
   230  exec-ddl
   231  CREATE TABLE wallet (
   232      id bigserial primary key,
   233      name text not null,
   234      gender int,
   235      email text,
   236      first_name text,
   237      last_name text,
   238      creation_date timestamp not null,
   239      situation int,
   240      balance decimal not null,
   241      is_blocked bool,
   242      INDEX (name),
   243      INDEX (situation),
   244      INDEX (is_blocked),
   245      INDEX (balance)
   246  );
   247  ----
   248  
   249  exec-ddl
   250  CREATE TABLE transaction (
   251      id bigserial primary key,
   252      sender_id bigint,
   253      receiver_id bigint,
   254      amount decimal not null,
   255      creation_date timestamp not null,
   256      last_update timestamp,
   257      schedule_date timestamp,
   258      status int,
   259      comment text,
   260      linked_trans_id bigint,
   261      c1 text,
   262      c2 text,
   263      c3 text,
   264      INDEX (sender_id),
   265      INDEX (receiver_id),
   266      INDEX (linked_trans_id)
   267  );
   268  ----
   269  
   270  opt
   271  SELECT * FROM transaction t
   272  JOIN wallet AS s on t.sender_id = s.id
   273  JOIN wallet AS r on t.receiver_id = r.id
   274  limit 10;
   275  ----
   276  limit
   277   ├── columns: id:1!null sender_id:2!null receiver_id:3!null amount:4!null creation_date:5!null last_update:6 schedule_date:7 status:8 comment:9 linked_trans_id:10 c1:11 c2:12 c3:13 id:14!null name:15!null gender:16 email:17 first_name:18 last_name:19 creation_date:20!null situation:21 balance:22!null is_blocked:23 id:24!null name:25!null gender:26 email:27 first_name:28 last_name:29 creation_date:30!null situation:31 balance:32!null is_blocked:33
   278   ├── cardinality: [0 - 10]
   279   ├── stats: [rows=10]
   280   ├── cost: 2357.55
   281   ├── key: (1)
   282   ├── fd: (1)-->(2-13), (14)-->(15-23), (2)==(14), (14)==(2), (24)-->(25-33), (3)==(24), (24)==(3)
   283   ├── inner-join (lookup wallet)
   284   │    ├── columns: t.id:1!null sender_id:2!null receiver_id:3!null amount:4!null t.creation_date:5!null last_update:6 schedule_date:7 status:8 comment:9 linked_trans_id:10 c1:11 c2:12 c3:13 s.id:14!null s.name:15!null s.gender:16 s.email:17 s.first_name:18 s.last_name:19 s.creation_date:20!null s.situation:21 s.balance:22!null s.is_blocked:23 r.id:24!null r.name:25!null r.gender:26 r.email:27 r.first_name:28 r.last_name:29 r.creation_date:30!null r.situation:31 r.balance:32!null r.is_blocked:33
   285   │    ├── key columns: [3] = [24]
   286   │    ├── lookup columns are key
   287   │    ├── stats: [rows=980.1, distinct(3)=98.9950071, null(3)=0, distinct(24)=98.9950071, null(24)=0]
   288   │    ├── cost: 2357.44
   289   │    ├── key: (1)
   290   │    ├── fd: (1)-->(2-13), (14)-->(15-23), (2)==(14), (14)==(2), (24)-->(25-33), (3)==(24), (24)==(3)
   291   │    ├── limit hint: 10.00
   292   │    ├── inner-join (lookup wallet)
   293   │    │    ├── columns: t.id:1!null sender_id:2!null receiver_id:3 amount:4!null t.creation_date:5!null last_update:6 schedule_date:7 status:8 comment:9 linked_trans_id:10 c1:11 c2:12 c3:13 s.id:14!null s.name:15!null s.gender:16 s.email:17 s.first_name:18 s.last_name:19 s.creation_date:20!null s.situation:21 s.balance:22!null s.is_blocked:23
   294   │    │    ├── key columns: [2] = [14]
   295   │    │    ├── lookup columns are key
   296   │    │    ├── stats: [rows=990, distinct(1)=628.605476, null(1)=0, distinct(2)=99, null(2)=0, distinct(3)=99.9950071, null(3)=9.9, distinct(4)=99.9950071, null(4)=0, distinct(5)=99.9950071, null(5)=0, distinct(14)=99, null(14)=0, distinct(15)=99.9950071, null(15)=0, distinct(20)=99.9950071, null(20)=0, distinct(22)=99.9950071, null(22)=0]
   297   │    │    ├── cost: 1739.63
   298   │    │    ├── key: (1)
   299   │    │    ├── fd: (1)-->(2-13), (14)-->(15-23), (2)==(14), (14)==(2)
   300   │    │    ├── limit hint: 100.00
   301   │    │    ├── scan t
   302   │    │    │    ├── columns: t.id:1!null sender_id:2 receiver_id:3 amount:4!null t.creation_date:5!null last_update:6 schedule_date:7 status:8 comment:9 linked_trans_id:10 c1:11 c2:12 c3:13
   303   │    │    │    ├── stats: [rows=1000, distinct(1)=1000, null(1)=0, distinct(2)=100, null(2)=10, distinct(3)=100, null(3)=10, distinct(4)=100, null(4)=0, distinct(5)=100, null(5)=0]
   304   │    │    │    ├── cost: 504.02
   305   │    │    │    ├── key: (1)
   306   │    │    │    ├── fd: (1)-->(2-13)
   307   │    │    │    └── limit hint: 200.00
   308   │    │    └── filters (true)
   309   │    └── filters (true)
   310   └── 10
   311  
   312  exec-ddl
   313  CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT, INDEX c_idx (c))
   314  ----
   315  
   316  exec-ddl
   317  ALTER TABLE abc INJECT STATISTICS '[
   318    {
   319      "columns": ["a"],
   320      "created_at": "2018-05-01 1:00:00.00000+00:00",
   321      "row_count": 500000000,
   322      "distinct_count": 500000000
   323    }
   324  ]'
   325  ----
   326  
   327  # Check that we choose the index join when it makes sense.
   328  opt
   329  SELECT * FROM abc WHERE c = 1
   330  ----
   331  index-join abc
   332   ├── columns: a:1!null b:2 c:3!null
   333   ├── stats: [rows=9.9000002, distinct(3)=1, null(3)=0]
   334   ├── cost: 50.609001
   335   ├── key: (1)
   336   ├── fd: ()-->(3), (1)-->(2)
   337   └── scan abc@c_idx
   338        ├── columns: a:1!null c:3!null
   339        ├── constraint: /3/1: [/1 - /1]
   340        ├── stats: [rows=9.9000002, distinct(3)=1, null(3)=0]
   341        ├── cost: 10.3060002
   342        ├── key: (1)
   343        └── fd: ()-->(3)
   344  
   345  # Regression test for #34810: make sure we pick the lookup join that uses
   346  # all equality columns.
   347  
   348  exec-ddl
   349  CREATE TABLE abcde (
   350    a TEXT NOT NULL,
   351    b UUID NOT NULL,
   352    c UUID NOT NULL,
   353    d VARCHAR(255) NOT NULL,
   354    e TEXT NOT NULL,
   355    UNIQUE INDEX idx_abd (a, b, d) STORING (c),
   356    UNIQUE INDEX idx_abcd (a, b, c, d)
   357  )
   358  ----
   359  
   360  exec-ddl
   361  ALTER TABLE abcde INJECT STATISTICS '[
   362    {
   363      "columns": ["a"],
   364      "created_at": "2019-02-08 04:10:40.001179+00:00",
   365      "row_count": 250000,
   366      "distinct_count": 1
   367    },
   368    {
   369      "columns": ["b"],
   370      "created_at": "2019-02-08 04:10:40.119954+00:00",
   371      "row_count": 250000,
   372      "distinct_count": 2
   373    },
   374    {
   375      "columns": ["d"],
   376      "created_at": "2019-02-08 04:10:40.119954+00:00",
   377      "row_count": 250000,
   378      "distinct_count": 125000
   379    }
   380  ]'
   381  ----
   382  
   383  exec-ddl
   384  CREATE TABLE wxyz (
   385    w TEXT NOT NULL,
   386    x UUID NOT NULL,
   387    y UUID NOT NULL,
   388    z TEXT NOT NULL
   389  )
   390  ----
   391  
   392  exec-ddl
   393  ALTER TABLE wxyz INJECT STATISTICS '[
   394    {
   395      "columns": ["w"],
   396      "created_at": "2019-02-08 04:10:40.001179+00:00",
   397      "row_count": 100,
   398      "distinct_count": 1
   399    },
   400    {
   401      "columns": ["x"],
   402      "created_at": "2019-02-08 04:10:40.119954+00:00",
   403      "row_count": 100,
   404      "distinct_count": 1
   405    },
   406    {
   407      "columns": ["y"],
   408      "created_at": "2019-02-08 04:10:40.119954+00:00",
   409      "row_count": 100,
   410      "distinct_count": 25
   411    }
   412  ]'
   413  ----
   414  
   415  opt
   416  SELECT w, x, y, z
   417  FROM wxyz
   418  INNER JOIN abcde
   419  ON w = a AND x = b AND y = c
   420  WHERE w = 'foo' AND x = '2AB23800-06B1-4E19-A3BB-DF3768B808D2'
   421  ----
   422  project
   423   ├── columns: w:1!null x:2!null y:3!null z:4!null
   424   ├── stats: [rows=500.488759]
   425   ├── cost: 3155.07489
   426   ├── fd: ()-->(1,2)
   427   └── inner-join (lookup abcde@idx_abcd)
   428        ├── columns: w:1!null x:2!null y:3!null z:4!null a:6!null b:7!null c:8!null
   429        ├── key columns: [1 2 3] = [6 7 8]
   430        ├── stats: [rows=500.488759, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=25, null(3)=0, distinct(6)=1, null(6)=0, distinct(7)=1, null(7)=0, distinct(8)=25, null(8)=0]
   431        ├── cost: 3150.06
   432        ├── fd: ()-->(1,2,6,7), (1)==(6), (6)==(1), (2)==(7), (7)==(2), (3)==(8), (8)==(3)
   433        ├── select
   434        │    ├── columns: w:1!null x:2!null y:3!null z:4!null
   435        │    ├── stats: [rows=100, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=25, null(3)=0, distinct(4)=10, null(4)=0]
   436        │    ├── cost: 110.03
   437        │    ├── fd: ()-->(1,2)
   438        │    ├── scan wxyz
   439        │    │    ├── columns: w:1!null x:2!null y:3!null z:4!null
   440        │    │    ├── stats: [rows=100, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=25, null(3)=0, distinct(4)=10, null(4)=0]
   441        │    │    └── cost: 109.02
   442        │    └── filters
   443        │         ├── w:1 = 'foo' [outer=(1), constraints=(/1: [/'foo' - /'foo']; tight), fd=()-->(1)]
   444        │         └── x:2 = '2ab23800-06b1-4e19-a3bb-df3768b808d2' [outer=(2), constraints=(/2: [/'2ab23800-06b1-4e19-a3bb-df3768b808d2' - /'2ab23800-06b1-4e19-a3bb-df3768b808d2']; tight), fd=()-->(2)]
   445        └── filters
   446             ├── a:6 = 'foo' [outer=(6), constraints=(/6: [/'foo' - /'foo']; tight), fd=()-->(6)]
   447             └── b:7 = '2ab23800-06b1-4e19-a3bb-df3768b808d2' [outer=(7), constraints=(/7: [/'2ab23800-06b1-4e19-a3bb-df3768b808d2' - /'2ab23800-06b1-4e19-a3bb-df3768b808d2']; tight), fd=()-->(7)]
   448  
   449  # Also for 34810: make sure the cost adjustment works when the estimated row
   450  # count is tiny.
   451  exec-ddl
   452  CREATE TABLE wxyzijklmn (
   453    w TEXT NOT NULL,
   454    x UUID NOT NULL,
   455    y UUID NOT NULL,
   456    z TEXT NOT NULL,
   457    i INT,
   458    j INT,
   459    k INT,
   460    l INT,
   461    m INT,
   462    n INT
   463  )
   464  ----
   465  
   466  exec-ddl
   467  ALTER TABLE wxyzijklmn INJECT STATISTICS '[
   468    {
   469      "columns": ["w"],
   470      "created_at": "2019-02-08 04:10:40.001179+00:00",
   471      "row_count": 10000,
   472      "distinct_count": 1
   473    },
   474    {
   475      "columns": ["x"],
   476      "created_at": "2019-02-08 04:10:40.119954+00:00",
   477      "row_count": 10000,
   478      "distinct_count": 1
   479    },
   480    {
   481      "columns": ["y"],
   482      "created_at": "2019-02-08 04:10:40.119954+00:00",
   483      "row_count": 10000,
   484      "distinct_count": 25
   485    },
   486    {
   487      "columns": ["i"],
   488      "created_at": "2019-02-08 04:10:40.119954+00:00",
   489      "row_count": 10000,
   490      "distinct_count": 10000
   491    },
   492    {
   493      "columns": ["j"],
   494      "created_at": "2019-02-08 04:10:40.119954+00:00",
   495      "row_count": 10000,
   496      "distinct_count": 10000
   497    },
   498    {
   499      "columns": ["k"],
   500      "created_at": "2019-02-08 04:10:40.119954+00:00",
   501      "row_count": 10000,
   502      "distinct_count": 10000
   503    },
   504    {
   505      "columns": ["l"],
   506      "created_at": "2019-02-08 04:10:40.119954+00:00",
   507      "row_count": 10000,
   508      "distinct_count": 10000
   509    },
   510    {
   511      "columns": ["m"],
   512      "created_at": "2019-02-08 04:10:40.119954+00:00",
   513      "row_count": 10000,
   514      "distinct_count": 10000
   515    },
   516    {
   517      "columns": ["n"],
   518      "created_at": "2019-02-08 04:10:40.119954+00:00",
   519      "row_count": 10000,
   520      "distinct_count": 10000
   521    }
   522  ]'
   523  ----
   524  
   525  opt
   526  SELECT w, x, y, z
   527  FROM wxyzijklmn
   528  INNER JOIN abcde
   529  ON w = a AND x = b AND y = c
   530  WHERE w = 'foo' AND x = '2AB23800-06B1-4E19-A3BB-DF3768B808D2' AND (i,j,k,l,m,n)=(1,2,3,4,5,6)
   531  ----
   532  project
   533   ├── columns: w:1!null x:2!null y:3!null z:4!null
   534   ├── stats: [rows=4.50439883]
   535   ├── cost: 12227.475
   536   ├── fd: ()-->(1,2)
   537   └── inner-join (lookup abcde@idx_abcd)
   538        ├── columns: w:1!null x:2!null y:3!null z:4!null i:5!null j:6!null k:7!null l:8!null m:9!null n:10!null a:12!null b:13!null c:14!null
   539        ├── key columns: [1 2 3] = [12 13 14]
   540        ├── stats: [rows=4.50439883, distinct(1)=0.9, null(1)=0, distinct(2)=0.9, null(2)=0, distinct(3)=0.884031733, null(3)=0, distinct(12)=0.9, null(12)=0, distinct(13)=0.9, null(13)=0, distinct(14)=0.884031733, null(14)=0]
   541        ├── cost: 12227.42
   542        ├── fd: ()-->(1,2,5-10,12,13), (1)==(12), (12)==(1), (2)==(13), (13)==(2), (3)==(14), (14)==(3)
   543        ├── select
   544        │    ├── columns: w:1!null x:2!null y:3!null z:4!null i:5!null j:6!null k:7!null l:8!null m:9!null n:10!null
   545        │    ├── stats: [rows=0.9, distinct(1)=0.9, null(1)=0, distinct(2)=0.9, null(2)=0, distinct(3)=0.884031733, null(3)=0, distinct(4)=0.899635587, null(4)=0, distinct(5)=0.9, null(5)=0, distinct(6)=0.9, null(6)=0, distinct(7)=0.9, null(7)=0, distinct(8)=0.9, null(8)=0, distinct(9)=0.9, null(9)=0, distinct(10)=0.9, null(10)=0, distinct(5-10)=0.9, null(5-10)=0]
   546        │    ├── cost: 12200.03
   547        │    ├── fd: ()-->(1,2,5-10)
   548        │    ├── scan wxyzijklmn
   549        │    │    ├── columns: w:1!null x:2!null y:3!null z:4!null i:5 j:6 k:7 l:8 m:9 n:10
   550        │    │    ├── stats: [rows=10000, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=25, null(3)=0, distinct(4)=1000, null(4)=0, distinct(5)=10000, null(5)=0, distinct(6)=10000, null(6)=0, distinct(7)=10000, null(7)=0, distinct(8)=10000, null(8)=0, distinct(9)=10000, null(9)=0, distinct(10)=10000, null(10)=0, distinct(5-10)=10000, null(5-10)=0]
   551        │    │    └── cost: 12100.02
   552        │    └── filters
   553        │         ├── w:1 = 'foo' [outer=(1), constraints=(/1: [/'foo' - /'foo']; tight), fd=()-->(1)]
   554        │         ├── x:2 = '2ab23800-06b1-4e19-a3bb-df3768b808d2' [outer=(2), constraints=(/2: [/'2ab23800-06b1-4e19-a3bb-df3768b808d2' - /'2ab23800-06b1-4e19-a3bb-df3768b808d2']; tight), fd=()-->(2)]
   555        │         ├── i:5 = 1 [outer=(5), constraints=(/5: [/1 - /1]; tight), fd=()-->(5)]
   556        │         ├── j:6 = 2 [outer=(6), constraints=(/6: [/2 - /2]; tight), fd=()-->(6)]
   557        │         ├── k:7 = 3 [outer=(7), constraints=(/7: [/3 - /3]; tight), fd=()-->(7)]
   558        │         ├── l:8 = 4 [outer=(8), constraints=(/8: [/4 - /4]; tight), fd=()-->(8)]
   559        │         ├── m:9 = 5 [outer=(9), constraints=(/9: [/5 - /5]; tight), fd=()-->(9)]
   560        │         └── n:10 = 6 [outer=(10), constraints=(/10: [/6 - /6]; tight), fd=()-->(10)]
   561        └── filters
   562             ├── a:12 = 'foo' [outer=(12), constraints=(/12: [/'foo' - /'foo']; tight), fd=()-->(12)]
   563             └── b:13 = '2ab23800-06b1-4e19-a3bb-df3768b808d2' [outer=(13), constraints=(/13: [/'2ab23800-06b1-4e19-a3bb-df3768b808d2' - /'2ab23800-06b1-4e19-a3bb-df3768b808d2']; tight), fd=()-->(13)]
   564  
   565  exec-ddl
   566  DROP TABLE abcde
   567  ----
   568  
   569  exec-ddl
   570  DROP TABLE wxyz
   571  ----
   572  
   573  exec-ddl
   574  CREATE TABLE abcde (
   575    a TEXT NOT NULL,
   576    b UUID NOT NULL,
   577    c UUID NOT NULL,
   578    d VARCHAR(255) NOT NULL,
   579    e TEXT NOT NULL,
   580    CONSTRAINT "primary" PRIMARY KEY (a, b, c),
   581    UNIQUE INDEX idx_abd (a, b, d),
   582    UNIQUE INDEX idx_abcd (a, b, c, d)
   583  )
   584  ----
   585  
   586  exec-ddl
   587  ALTER TABLE abcde INJECT STATISTICS '[
   588    {
   589      "columns": ["a"],
   590      "created_at": "2019-02-08 04:10:40.001179+00:00",
   591      "row_count": 250000,
   592      "distinct_count": 1
   593    },
   594    {
   595      "columns": ["b"],
   596      "created_at": "2019-02-08 04:10:40.119954+00:00",
   597      "row_count": 250000,
   598      "distinct_count": 2
   599    },
   600    {
   601      "columns": ["d"],
   602      "created_at": "2019-02-08 04:10:40.119954+00:00",
   603      "row_count": 250000,
   604      "distinct_count": 125000
   605    }
   606  ]'
   607  ----
   608  
   609  exec-ddl
   610  CREATE TABLE wxyz (
   611    w TEXT NOT NULL,
   612    x UUID NOT NULL,
   613    y UUID NOT NULL,
   614    z TEXT NOT NULL,
   615    CONSTRAINT "primary" PRIMARY KEY (w, x, y),
   616    CONSTRAINT "foreign" FOREIGN KEY (w, x, y) REFERENCES abcde (a, b, c)
   617  )
   618  ----
   619  
   620  exec-ddl
   621  ALTER TABLE wxyz INJECT STATISTICS '[
   622    {
   623      "columns": ["w"],
   624      "created_at": "2019-02-08 04:10:40.001179+00:00",
   625      "row_count": 10000,
   626      "distinct_count": 1
   627    },
   628    {
   629      "columns": ["x"],
   630      "created_at": "2019-02-08 04:10:40.119954+00:00",
   631      "row_count": 10000,
   632      "distinct_count": 1
   633    },
   634    {
   635      "columns": ["y"],
   636      "created_at": "2019-02-08 04:10:40.119954+00:00",
   637      "row_count": 10000,
   638      "distinct_count": 2500
   639    }
   640  ]'
   641  ----
   642  
   643  # Regression test for #34811. Ensure the soft limit propagation causes us to
   644  # select a lookup join.
   645  opt
   646  SELECT w, x, y, z
   647  FROM wxyz
   648  INNER JOIN abcde
   649  ON w = a AND x = b AND y = c
   650  WHERE w = 'foo' AND x = '2AB23800-06B1-4E19-A3BB-DF3768B808D2'
   651  ORDER BY d
   652  LIMIT 10
   653  ----
   654  project
   655   ├── columns: w:1!null x:2!null y:3!null z:4!null  [hidden: d:8!null]
   656   ├── cardinality: [0 - 10]
   657   ├── stats: [rows=10]
   658   ├── cost: 699.541329
   659   ├── key: (8)
   660   ├── fd: ()-->(1,2), (3)-->(4,8), (8)-->(3,4)
   661   ├── ordering: +8 opt(1,2) [actual: +8]
   662   └── limit
   663        ├── columns: w:1!null x:2!null y:3!null z:4!null a:5!null b:6!null c:7!null d:8!null
   664        ├── internal-ordering: +8 opt(1,2,5,6)
   665        ├── cardinality: [0 - 10]
   666        ├── stats: [rows=10]
   667        ├── cost: 699.431329
   668        ├── key: (7)
   669        ├── fd: ()-->(1,2,5,6), (3)-->(4), (7)-->(8), (8)-->(7), (1)==(5), (5)==(1), (2)==(6), (6)==(2), (3)==(7), (7)==(3)
   670        ├── ordering: +8 opt(1,2,5,6) [actual: +8]
   671        ├── inner-join (lookup wxyz)
   672        │    ├── columns: w:1!null x:2!null y:3!null z:4!null a:5!null b:6!null c:7!null d:8!null
   673        │    ├── key columns: [5 6 7] = [1 2 3]
   674        │    ├── lookup columns are key
   675        │    ├── stats: [rows=50048.8759, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=2500, null(3)=0, distinct(5)=1, null(5)=0, distinct(6)=1, null(6)=0, distinct(7)=2500, null(7)=0]
   676        │    ├── cost: 699.321329
   677        │    ├── key: (7)
   678        │    ├── fd: ()-->(1,2,5,6), (3)-->(4), (7)-->(8), (8)-->(7), (1)==(5), (5)==(1), (2)==(6), (6)==(2), (3)==(7), (7)==(3)
   679        │    ├── ordering: +8 opt(1,2,5,6) [actual: +8]
   680        │    ├── limit hint: 10.00
   681        │    ├── scan abcde@idx_abd
   682        │    │    ├── columns: a:5!null b:6!null c:7!null d:8!null
   683        │    │    ├── constraint: /5/6/8: [/'foo'/'2ab23800-06b1-4e19-a3bb-df3768b808d2' - /'foo'/'2ab23800-06b1-4e19-a3bb-df3768b808d2']
   684        │    │    ├── stats: [rows=125000, distinct(5)=1, null(5)=0, distinct(6)=1, null(6)=0, distinct(7)=24975.5859, null(7)=0, distinct(8)=93750, null(8)=0]
   685        │    │    ├── cost: 216.01
   686        │    │    ├── key: (7)
   687        │    │    ├── fd: ()-->(5,6), (7)-->(8), (8)-->(7)
   688        │    │    ├── ordering: +8 opt(5,6) [actual: +8]
   689        │    │    └── limit hint: 100.00
   690        │    └── filters
   691        │         ├── w:1 = 'foo' [outer=(1), constraints=(/1: [/'foo' - /'foo']; tight), fd=()-->(1)]
   692        │         └── x:2 = '2ab23800-06b1-4e19-a3bb-df3768b808d2' [outer=(2), constraints=(/2: [/'2ab23800-06b1-4e19-a3bb-df3768b808d2' - /'2ab23800-06b1-4e19-a3bb-df3768b808d2']; tight), fd=()-->(2)]
   693        └── 10