index-candidates-recommendations

exec-ddl
CREATE TABLE t1 (k INT, i INT, f FLOAT, s STRING)
----

exec-ddl
CREATE TABLE t2 (k INT, i INT, s STRING)
----

exec-ddl
CREATE TABLE t3 (k INT, i INT, f FLOAT)
----

exec-ddl
CREATE TABLE t4 (k INT, f FLOAT, j JSONB, a INT[])
----

# Ensure that columns that require inverted indexes do not have candidates. This
# is temporary until we support inverted index recommendations.

index-candidates
SELECT k, f FROM t4 WHERE j IS NULL
----

index-candidates
SELECT * FROM t4 WHERE k = 2 AND a IS NULL OR f > 2
----
t4:
 (f)
 (k)
 (k, f)

# Ensure that new indexes do not get recommended if an identical existing
# index exists.

exec-ddl
CREATE INDEX existing_t1_k ON t1(k) STORING (s)
----

exec-ddl
CREATE INDEX existing_t1_i ON t1(i)
----

exec-ddl
CREATE INDEX existing_t2_k ON t2(k)
----

index-recommendations format=show-cost
SELECT k FROM t1 WHERE k >= 3
----
No index recommendations.
--
Optimal Plan.
scan t1@_hyp_1
 ├── columns: k:1(int!null)
 ├── constraint: /1/5: [/3 - ]
 └── cost: 367.353333

# No recommendations because an identical index exists already.
index-recommendations
SELECT i FROM t1 WHERE i >= 3
----
No index recommendations.
--
Optimal Plan.
scan t1@_hyp_1
 ├── columns: i:2(int!null)
 └── constraint: /2/5: [/3 - ]

# No recommendations because an index with the same explicit columns exists
# already, and no new columns are being stored.
index-recommendations
SELECT k FROM t1 WHERE k >= 3
----
No index recommendations.
--
Optimal Plan.
scan t1@_hyp_1
 ├── columns: k:1(int!null)
 └── constraint: /1/5: [/3 - ]


index-recommendations format=show-cost
SELECT t1.k FROM t1 JOIN t2 ON t1.k = t2.k WHERE t1.i > 3
----
index recommendations: 1
1. type: index replacement
   SQL commands: DROP INDEX t1@existing_t1_i; CREATE INDEX ON t1 (i) STORING (k);
--
Optimal Plan.
project
 ├── columns: k:1(int!null)
 ├── cost: 1528.93403
 └── inner-join (hash)
      ├── columns: t1.k:1(int!null) t1.i:2(int!null) t2.k:8(int!null)
      ├── cost: 1496.24403
      ├── fd: (1)==(8), (8)==(1)
      ├── scan t2
      │    ├── columns: t2.k:8(int)
      │    └── cost: 1074.52
      ├── scan t1@_hyp_2
      │    ├── columns: t1.k:1(int) t1.i:2(int!null)
      │    ├── constraint: /2/5: [/4 - ]
      │    └── cost: 370.686667
      └── filters
           └── eq [type=bool, outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]
                ├── variable: t1.k:1 [type=int]
                └── variable: t2.k:8 [type=int]

# There is a replacement recommendation because an index with the same explicit
# columns exists already and new columns are being stored here. We stored the
# existing index's stored columns and any new stored columns.
index-recommendations
SELECT i FROM t1 WHERE k >= 3
----
index recommendations: 1
1. type: index replacement
   SQL commands: DROP INDEX t1@existing_t1_k; CREATE INDEX ON t1 (k) STORING (i, s);
--
Optimal Plan.
project
 ├── columns: i:2(int)
 └── scan t1@_hyp_1
      ├── columns: k:1(int!null) i:2(int)
      └── constraint: /1/5: [/3 - ]

index-recommendations
SELECT t1.k FROM t1 JOIN t2 ON t1.k = t2.k WHERE t1.i > 3 AND t2.i > 3
----
index recommendations: 2
1. type: index replacement
   SQL commands: DROP INDEX t1@existing_t1_i; CREATE INDEX ON t1 (i) STORING (k);
2. type: index creation
   SQL command: CREATE INDEX ON t2 (i) STORING (k);
--
Optimal Plan.
project
 ├── columns: k:1(int!null)
 └── inner-join (hash)
      ├── columns: t1.k:1(int!null) t1.i:2(int!null) t2.k:8(int!null) t2.i:9(int!null)
      ├── fd: (1)==(8), (8)==(1)
      ├── scan t1@_hyp_2
      │    ├── columns: t1.k:1(int) t1.i:2(int!null)
      │    └── constraint: /2/5: [/4 - ]
      ├── scan t2@_hyp_2
      │    ├── columns: t2.k:8(int) t2.i:9(int!null)
      │    └── constraint: /9/11: [/4 - ]
      └── filters
           └── eq [type=bool, outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]
                ├── variable: t1.k:1 [type=int]
                └── variable: t2.k:8 [type=int]

exec-ddl
DROP INDEX t1@existing_t1_k
----

exec-ddl
DROP INDEX t1@existing_t1_i
----

exec-ddl
DROP INDEX t2@existing_t2_k
----

# Below are tests without existing indexes on the tables. Every query has two
# tests, one showing its index candidates and the other showing its final index
# recommendations.

# Basic tests for comparison operator, range, equality, join, order by, and
# group by candidates.

index-candidates
SELECT * FROM t1 WHERE i >= 3
----
t1:
 (i)


index-recommendations format=show-cost
SELECT i FROM t1 WHERE i >= 3
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t1 (i);
--
Optimal Plan.
scan t1@_hyp_1
 ├── columns: i:2(int!null)
 ├── constraint: /2/5: [/3 - ]
 └── cost: 367.353333

index-recommendations
SELECT * FROM t1 WHERE i >= 3
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t1 (i) STORING (k, f, s);
--
Optimal Plan.
scan t1@_hyp_1
 ├── columns: k:1(int) i:2(int!null) f:3(float) s:4(string)
 └── constraint: /2/5: [/3 - ]


index-candidates
SELECT f, k FROM t1 WHERE f > 2 AND f < 8
----
t1:
 (f)

index-recommendations format=show-cost
SELECT f FROM t1 WHERE f > 2 AND f < 8
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t1 (f);
--
Optimal Plan.
scan t1@_hyp_1
 ├── columns: f:3(float!null)
 ├── constraint: /3/5: [/2.0000000000000004 - /7.999999999999999]
 └── cost: 131.797778

index-recommendations
SELECT f, k FROM t1 WHERE f > 2 AND f < 8
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t1 (f) STORING (k);
--
Optimal Plan.
scan t1@_hyp_1
 ├── columns: f:3(float!null) k:1(int)
 └── constraint: /3/5: [/2.0000000000000004 - /7.999999999999999]


index-candidates
SELECT s FROM t1 WHERE s = 'NG'
----
t1:
 (s)

index-recommendations format=show-cost
SELECT s FROM t1 WHERE s = 'NG'
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t1 (s);
--
Optimal Plan.
scan t1@_hyp_1
 ├── columns: s:4(string!null)
 ├── constraint: /4/5: [/'NG' - /'NG']
 ├── cost: 24.62
 └── fd: ()-->(4)

index-candidates
SELECT t1.k FROM t1 JOIN t2 ON t1.k = t2.i
----
t1:
 (k)
t2:
 (i)

index-recommendations format=show-cost
SELECT t1.k FROM t1 JOIN t2 ON t1.k = t2.i
----
index recommendations: 2
1. type: index creation
   SQL command: CREATE INDEX ON t1 (k);
2. type: index creation
   SQL command: CREATE INDEX ON t2 (i);
--
Optimal Plan.
project
 ├── columns: k:1(int!null)
 ├── cost: 2375.2
 └── inner-join (merge)
      ├── columns: t1.k:1(int!null) t2.i:9(int!null)
      ├── left ordering: +1
      ├── right ordering: +9
      ├── cost: 2277.17
      ├── fd: (1)==(9), (9)==(1)
      ├── scan t1@_hyp_1
      │    ├── columns: t1.k:1(int)
      │    ├── cost: 1084.62
      │    └── ordering: +1
      ├── scan t2@_hyp_1
      │    ├── columns: t2.i:9(int)
      │    ├── cost: 1074.52
      │    └── ordering: +9
      └── filters (true)


index-candidates
SELECT t2.s FROM t1 RIGHT JOIN t2 ON t1.s LIKE t2.s
----
t1:
 (s)
t2:
 (s)


index-recommendations format=show-cost
SELECT t2.s FROM t1 RIGHT JOIN t2 ON t1.s LIKE t2.s
----
No index recommendations.
--
Optimal Plan.
project
 ├── columns: s:10(string)
 ├── cost: 15522.6696
 └── left-join (cross)
      ├── columns: t1.s:4(string) t2.s:10(string)
      ├── cost: 12189.3163
      ├── scan t2
      │    ├── columns: t2.s:10(string)
      │    └── cost: 1074.52
      ├── scan t1
      │    ├── columns: t1.s:4(string)
      │    └── cost: 1084.62
      └── filters
           └── like [type=bool, outer=(4,10), constraints=(/4: (/NULL - ]; /10: (/NULL - ])]
                ├── variable: t1.s:4 [type=string]
                └── variable: t2.s:10 [type=string]


# See function comment in indexrec.FindIndexRecommendationSet for an explanation
# as to why there is no recommendation for an index on s.
index-recommendations
SELECT t2.s FROM t1 RIGHT JOIN t2 ON t1.s LIKE t2.s
----
No index recommendations.
--
Optimal Plan.
project
 ├── columns: s:10(string)
 └── left-join (cross)
      ├── columns: t1.s:4(string) t2.s:10(string)
      ├── scan t2
      │    └── columns: t2.s:10(string)
      ├── scan t1
      │    └── columns: t1.s:4(string)
      └── filters
           └── like [type=bool, outer=(4,10), constraints=(/4: (/NULL - ]; /10: (/NULL - ])]
                ├── variable: t1.s:4 [type=string]
                └── variable: t2.s:10 [type=string]

index-candidates
SELECT i FROM t1 ORDER BY i
----
t1:
 (i)

index-recommendations format=show-cost
SELECT i FROM t1 ORDER BY i
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t1 (i);
--
Optimal Plan.
scan t1@_hyp_1
 ├── columns: i:2(int)
 ├── cost: 1084.62
 └── ordering: +2

index-candidates
SELECT k, i FROM t1 ORDER BY k DESC, i ASC
----
t1:
 (k, i DESC)

index-recommendations format=show-cost
SELECT k, i FROM t1 ORDER BY k DESC, i ASC
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t1 (k, i DESC);
--
Optimal Plan.
scan t1@_hyp_1,rev
 ├── columns: k:1(int) i:2(int)
 ├── cost: 1195.37442
 └── ordering: -1,+2

index-candidates
SELECT count(*) FROM t1 GROUP BY k
----
t1:
 (k)

index-recommendations format=show-cost
SELECT count(*) FROM t1 GROUP BY k
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t1 (k);
--
Optimal Plan.
project
 ├── columns: count:8(int!null)
 ├── cost: 1106.67
 └── group-by (streaming)
      ├── columns: k:1(int) count_rows:8(int!null)
      ├── grouping columns: k:1(int)
      ├── internal-ordering: +1
      ├── cost: 1105.65
      ├── key: (1)
      ├── fd: (1)-->(8)
      ├── scan t1@_hyp_1
      │    ├── columns: k:1(int)
      │    ├── cost: 1084.62
      │    └── ordering: +1
      └── aggregations
           └── count-rows [as=count_rows:8, type=int]

index-candidates
SELECT sum(k) FROM t1 GROUP BY i, f, k
----
t1:
 (k, i, f)

index-recommendations format=show-cost
SELECT sum(k) FROM t1 GROUP BY i, f, k
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t1 (k, i, f);
--
Optimal Plan.
project
 ├── columns: sum:8(decimal)
 ├── cost: 1164.87
 └── group-by (streaming)
      ├── columns: k:1(int) i:2(int) f:3(float) sum:8(decimal)
      ├── grouping columns: k:1(int) i:2(int) f:3(float)
      ├── internal-ordering: +1,+2,+3
      ├── cost: 1154.85
      ├── key: (1-3)
      ├── fd: (1-3)-->(8)
      ├── scan t1@_hyp_1
      │    ├── columns: k:1(int) i:2(int) f:3(float)
      │    ├── cost: 1104.82
      │    └── ordering: +1,+2,+3
      └── aggregations
           └── sum [as=sum:8, type=decimal, outer=(1)]
                └── variable: k:1 [type=int]

# Test joins with more complex predicates. See rule 3 and rule 4 in
# indexrec.FindIndexCandidates.

index-candidates
SELECT t1.f, t2.k, t2.i
FROM t1 FULL JOIN t2
ON t2.k IS NULL
AND t1.f::STRING NOT LIKE t2.i::STRING
----
t1:
 (f)
t2:
 (i)
 (k)
 (k, i)

index-recommendations format=show-cost
SELECT t1.f, t2.k, t2.i
FROM t1 FULL JOIN t2
ON t2.k IS NULL
AND t1.f::STRING NOT LIKE t2.i::STRING
----
No index recommendations.
--
Optimal Plan.
full-join (cross)
 ├── columns: f:3(float) k:8(int) i:9(int)
 ├── stable
 ├── cost: 12199.4263
 ├── scan t1
 │    ├── columns: f:3(float)
 │    └── cost: 1084.62
 ├── scan t2
 │    ├── columns: t2.k:8(int) t2.i:9(int)
 │    └── cost: 1084.62
 └── filters
      ├── is [type=bool, outer=(8), constraints=(/8: [/NULL - /NULL]; tight), fd=()-->(8)]
      │    ├── variable: t2.k:8 [type=int]
      │    └── null [type=unknown]
      └── not-like [type=bool, outer=(3,9), stable]
           ├── cast: STRING [type=string]
           │    └── variable: f:3 [type=float]
           └── cast: STRING [type=string]
                └── variable: t2.i:9 [type=int]

index-candidates
SELECT t1.k, t1.s, t2.k, t2.i
FROM t1 LEFT JOIN t2
ON t1.k != t2.k
AND t1.s IS NOT NULL
AND t2.i IS NULL
----
t1:
 (k)
 (k, s)
 (s)
t2:
 (i)
 (i, k)
 (k)

index-recommendations format=show-cost
SELECT t1.k, t1.s, t2.k, t2.i
FROM t1 LEFT JOIN t2
ON t1.k != t2.k
AND t1.s IS NOT NULL
AND t2.i IS NULL
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t2 (i) STORING (k);
--
Optimal Plan.
left-join (cross)
 ├── columns: k:1(int) s:4(string) k:8(int) i:9(int)
 ├── cost: 1232.055
 ├── scan t1
 │    ├── columns: t1.k:1(int) t1.s:4(string)
 │    └── cost: 1094.72
 ├── scan t2@_hyp_1
 │    ├── columns: t2.k:8(int) t2.i:9(int)
 │    ├── constraint: /9/11: [/NULL - /NULL]
 │    ├── cost: 24.6200001
 │    └── fd: ()-->(9)
 └── filters
      ├── ne [type=bool, outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ])]
      │    ├── variable: t1.k:1 [type=int]
      │    └── variable: t2.k:8 [type=int]
      └── is-not [type=bool, outer=(4), constraints=(/4: (/NULL - ]; tight)]
           ├── variable: t1.s:4 [type=string]
           └── null [type=unknown]

index-candidates
SELECT i FROM t1 WHERE k < 3 AND i > 5
----
t1:
 (i)
 (k)

index-recommendations
SELECT i FROM t1 WHERE k < 3 AND i > 5
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t1 (k) STORING (i);
--
Optimal Plan.
project
 ├── columns: i:2(int!null)
 └── select
      ├── columns: k:1(int!null) i:2(int!null)
      ├── scan t1@_hyp_1
      │    ├── columns: k:1(int!null) i:2(int)
      │    └── constraint: /1/5: (/NULL - /2]
      └── filters
           └── gt [type=bool, outer=(2), constraints=(/2: [/6 - ]; tight)]
                ├── variable: i:2 [type=int]
                └── const: 5 [type=int]

index-candidates
SELECT i FROM t1 WHERE k < 3 AND i > 5 OR f < 7
----
t1:
 (f)
 (i)
 (k)

index-recommendations
SELECT i FROM t1 WHERE k < 3 AND i > 5 OR f < 7
----
index recommendations: 2
1. type: index creation
   SQL command: CREATE INDEX ON t1 (k) STORING (i, f);
2. type: index creation
   SQL command: CREATE INDEX ON t1 (f) STORING (k, i);
--
Optimal Plan.
project
 ├── columns: i:2(int)
 └── project
      ├── columns: k:1(int) i:2(int) f:3(float)
      └── distinct-on
           ├── columns: k:1(int) i:2(int) f:3(float) rowid:5(int!null)
           ├── grouping columns: rowid:5(int!null)
           ├── key: (5)
           ├── fd: (5)-->(1-3)
           ├── union-all
           │    ├── columns: k:1(int) i:2(int) f:3(float) rowid:5(int!null)
           │    ├── left columns: k:8(int) i:9(int) f:10(float) rowid:12(int)
           │    ├── right columns: k:15(int) i:16(int) f:17(float) rowid:19(int)
           │    ├── select
           │    │    ├── columns: k:8(int!null) i:9(int!null) f:10(float) rowid:12(int!null)
           │    │    ├── key: (12)
           │    │    ├── fd: (12)-->(8-10)
           │    │    ├── scan t1@_hyp_1
           │    │    │    ├── columns: k:8(int!null) i:9(int) f:10(float) rowid:12(int!null)
           │    │    │    ├── constraint: /8/12: (/NULL - /2]
           │    │    │    ├── key: (12)
           │    │    │    └── fd: (12)-->(8-10)
           │    │    └── filters
           │    │         └── gt [type=bool, outer=(9), constraints=(/9: [/6 - ]; tight)]
           │    │              ├── variable: i:9 [type=int]
           │    │              └── const: 5 [type=int]
           │    └── scan t1@_hyp_3
           │         ├── columns: k:15(int) i:16(int) f:17(float!null) rowid:19(int!null)
           │         ├── constraint: /17/19: (/NULL - /6.999999999999999]
           │         ├── key: (19)
           │         └── fd: (19)-->(15-17)
           └── aggregations
                ├── const-agg [as=k:1, type=int, outer=(1)]
                │    └── variable: k:1 [type=int]
                ├── const-agg [as=i:2, type=int, outer=(2)]
                │    └── variable: i:2 [type=int]
                └── const-agg [as=f:3, type=float, outer=(3)]
                     └── variable: f:3 [type=float]

# Test more complex queries. See rule 5 in indexrec.FindIndexCandidates. The
# aspects of rule 5 that are demonstrated by each test are highlighted the
# test's comment.

# Multi-column combinations used: EQ.
index-candidates
SELECT k, i FROM t1 WHERE k = 1 AND i = 2
----
t1:
 (i)
 (k)
 (k, i)

index-recommendations format=show-cost
SELECT k, i FROM t1 WHERE k = 1 AND i = 2
----
index recommendations: 2
1. type: index creation
   SQL command: CREATE INDEX ON t1 (k) STORING (i);
2. type: index creation
   SQL command: CREATE INDEX ON t1 (i) STORING (k);
--
Optimal Plan.
inner-join (zigzag t1@_hyp_1 t1@_hyp_2)
 ├── columns: k:1(int!null) i:2(int!null)
 ├── eq columns: [5] = [5]
 ├── left fixed columns: [1] = [1]
 ├── right fixed columns: [2] = [2]
 ├── cost: 11.9982432
 ├── fd: ()-->(1,2)
 └── filters
      ├── eq [type=bool, outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)]
      │    ├── variable: k:1 [type=int]
      │    └── const: 1 [type=int]
      └── eq [type=bool, outer=(2), constraints=(/2: [/2 - /2]; tight), fd=()-->(2)]
           ├── variable: i:2 [type=int]
           └── const: 2 [type=int]

# Multi-column combinations used: EQ + R.
index-candidates
SELECT * FROM t1 WHERE k = 1 AND f > 0
----
t1:
 (f)
 (k)
 (k, f)

index-recommendations format=show-cost
SELECT * FROM t1 WHERE k = 1 AND f > 0
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t1 (k, f) STORING (i, s);
--
Optimal Plan.
scan t1@_hyp_3
 ├── columns: k:1(int!null) i:2(int) f:3(float!null) s:4(string)
 ├── constraint: /1/3/5: [/1/5e-324 - /1]
 ├── cost: 24.1933333
 └── fd: ()-->(1)


# Multi-column combinations used: EQ, EQ + R.
index-candidates
SELECT  k, i, f FROM t1 WHERE k = 1 AND i = 2 AND f > 0
----
t1:
 (f)
 (i)
 (k)
 (k, i)
 (k, i, f)

index-recommendations format=show-cost
SELECT k, i, f FROM t1 WHERE k = 1 AND i = 2 AND f > 0
----
index recommendations: 2
1. type: index creation
   SQL command: CREATE INDEX ON t1 (k) STORING (i, f);
2. type: index creation
   SQL command: CREATE INDEX ON t1 (i) STORING (k, f);
--
Optimal Plan.
inner-join (zigzag t1@_hyp_1 t1@_hyp_2)
 ├── columns: k:1(int!null) i:2(int!null) f:3(float!null)
 ├── eq columns: [5] = [5]
 ├── left fixed columns: [1] = [1]
 ├── right fixed columns: [2] = [2]
 ├── cost: 11.8278162
 ├── fd: ()-->(1,2)
 └── filters
      ├── eq [type=bool, outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)]
      │    ├── variable: k:1 [type=int]
      │    └── const: 1 [type=int]
      ├── eq [type=bool, outer=(2), constraints=(/2: [/2 - /2]; tight), fd=()-->(2)]
      │    ├── variable: i:2 [type=int]
      │    └── const: 2 [type=int]
      └── gt [type=bool, outer=(3), constraints=(/3: [/5e-324 - ]; tight)]
           ├── variable: f:3 [type=float]
           └── const: 0.0 [type=float]

# Multi-column combinations used: J + R.
index-candidates
SELECT t1.k, t1.f FROM t1 JOIN t2 ON t1.k != t2.k WHERE t1.f > 0
----
t1:
 (f)
 (k)
 (k, f)
t2:
 (k)

index-recommendations format=show-cost
SELECT t1.k, t1.f FROM t1 JOIN t2 ON t1.k != t2.k WHERE t1.f > 0
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t1 (f) STORING (k);
--
Optimal Plan.
project
 ├── columns: k:1(int!null) f:3(float!null)
 ├── cost: 5885.93736
 └── inner-join (cross)
      ├── columns: t1.k:1(int!null) f:3(float!null) t2.k:8(int!null)
      ├── cost: 4796.91736
      ├── scan t2
      │    ├── columns: t2.k:8(int)
      │    └── cost: 1074.52
      ├── scan t1@_hyp_1
      │    ├── columns: t1.k:1(int) f:3(float!null)
      │    ├── constraint: /3/5: [/5e-324 - ]
      │    └── cost: 370.686667
      └── filters
           └── ne [type=bool, outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ])]
                ├── variable: t1.k:1 [type=int]
                └── variable: t2.k:8 [type=int]

# Multi-column combinations used: EQ, EQ + J.
index-candidates
SELECT t1.i, t1.s FROM t1 JOIN t2 ON t1.k != t2.k WHERE t1.i = 2 AND t1.s = 'NG'
----
t1:
 (i)
 (i, s)
 (i, s, k)
 (k)
 (s)
t2:
 (k)

index-recommendations format=show-cost
SELECT t1.i, t1.s FROM t1 JOIN t2 ON t1.k != t2.k WHERE t1.i = 2 AND t1.s = 'NG'
----
index recommendations: 2
1. type: index creation
   SQL command: CREATE INDEX ON t1 (i) STORING (k, s);
2. type: index creation
   SQL command: CREATE INDEX ON t1 (s) STORING (k, i);
--
Optimal Plan.
project
 ├── columns: i:2(int!null) s:4(string!null)
 ├── cost: 1111.17702
 ├── fd: ()-->(2,4)
 └── inner-join (cross)
      ├── columns: t1.k:1(int!null) t1.i:2(int!null) t1.s:4(string!null) t2.k:8(int!null)
      ├── cost: 1108.1814
      ├── fd: ()-->(2,4)
      ├── scan t2
      │    ├── columns: t2.k:8(int)
      │    └── cost: 1074.52
      ├── inner-join (zigzag t1@_hyp_1 t1@_hyp_2)
      │    ├── columns: t1.k:1(int) t1.i:2(int!null) t1.s:4(string!null)
      │    ├── eq columns: [5] = [5]
      │    ├── left fixed columns: [2] = [2]
      │    ├── right fixed columns: [4] = ['NG']
      │    ├── cost: 12.0073514
      │    ├── fd: ()-->(2,4)
      │    └── filters
      │         ├── eq [type=bool, outer=(2), constraints=(/2: [/2 - /2]; tight), fd=()-->(2)]
      │         │    ├── variable: t1.i:2 [type=int]
      │         │    └── const: 2 [type=int]
      │         └── eq [type=bool, outer=(4), constraints=(/4: [/'NG' - /'NG']; tight), fd=()-->(4)]
      │              ├── variable: t1.s:4 [type=string]
      │              └── const: 'NG' [type=string]
      └── filters
           └── ne [type=bool, outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ])]
                ├── variable: t1.k:1 [type=int]
                └── variable: t2.k:8 [type=int]

# Multi-column combinations used: EQ, EQ + R, J + R, EQ + J, EQ + J + R.
index-candidates
SELECT count(*)
FROM t1 LEFT JOIN t2
ON t1.k != t2.k
GROUP BY t2.s, t2.i
UNION ALL
SELECT count(*)
FROM (
  SELECT *
  FROM t1
  WHERE t1.f > t1.i
  AND t1.s = 'NG'
)
----
t1:
 (f)
 (i)
 (k)
 (k, f)
 (k, i)
 (s)
 (s, f)
 (s, i)
 (s, k)
 (s, k, f)
 (s, k, i)
t2:
 (i, s)
 (k)

index-recommendations format=show-cost
SELECT count(*)
FROM t1 LEFT JOIN t2
ON t1.k != t2.k
GROUP BY t2.s, t2.i
UNION ALL
SELECT count(*)
FROM (
  SELECT *
  FROM t1
  WHERE t1.f > t1.i
  AND t1.s = 'NG'
)
----
index recommendations: 1
1. type: index creation
   SQL command: CREATE INDEX ON t1 (s, f) STORING (i);
--
Optimal Plan.
union-all
 ├── columns: count:23(int!null)
 ├── left columns: count_rows:14(int)
 ├── right columns: count_rows:22(int)
 ├── cardinality: [1 - ]
 ├── cost: 25598.0782
 ├── project
 │    ├── columns: count_rows:14(int!null)
 │    ├── cost: 25563.0458
 │    └── group-by (hash)
 │         ├── columns: t2.i:9(int) t2.s:10(string) count_rows:14(int!null)
 │         ├── grouping columns: t2.i:9(int) t2.s:10(string)
 │         ├── cost: 25553.0258
 │         ├── key: (9,10)
 │         ├── fd: (9,10)-->(14)
 │         ├── left-join (cross)
 │         │    ├── columns: t1.k:1(int) t2.k:8(int) t2.i:9(int) t2.s:10(string)
 │         │    ├── cost: 12209.5163
 │         │    ├── scan t1
 │         │    │    ├── columns: t1.k:1(int)
 │         │    │    └── cost: 1084.62
 │         │    ├── scan t2
 │         │    │    ├── columns: t2.k:8(int) t2.i:9(int) t2.s:10(string)
 │         │    │    └── cost: 1094.72
 │         │    └── filters
 │         │         └── ne [type=bool, outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ])]
 │         │              ├── variable: t1.k:1 [type=int]
 │         │              └── variable: t2.k:8 [type=int]
 │         └── aggregations
 │              └── count-rows [as=count_rows:14, type=int]
 └── scalar-group-by
      ├── columns: count_rows:22(int!null)
      ├── cardinality: [1 - 1]
      ├── cost: 25.002367
      ├── key: ()
      ├── fd: ()-->(22)
      ├── select
      │    ├── columns: t1.i:16(int!null) f:17(float!null) t1.s:18(string!null)
      │    ├── cost: 24.9391
      │    ├── fd: ()-->(18)
      │    ├── scan t1@_hyp_5
      │    │    ├── columns: t1.i:16(int) f:17(float!null) t1.s:18(string!null)
      │    │    ├── constraint: /18/17/19: (/'NG'/NULL - /'NG']
      │    │    ├── cost: 24.8092
      │    │    └── fd: ()-->(18)
      │    └── filters
      │         └── gt [type=bool, outer=(16,17), constraints=(/16: (/NULL - ]; /17: (/NULL - ])]
      │              ├── variable: f:17 [type=float]
      │              └── variable: t1.i:16 [type=int]
      └── aggregations
           └── count-rows [as=count_rows:22, type=int]

# No rule 5 multi-column index combinations.
index-candidates
SELECT t1.k, t1.i, t2.i
FROM t1 LEFT JOIN t2
ON t1.k = t2.k
WHERE EXISTS (SELECT * FROM t3 WHERE t3.f > t3.k)
ORDER BY t1.k, t2.i, t1.i DESC
----
t1:
 (k)
 (k, i DESC)
t2:
 (i)
 (k)
t3:
 (f)
 (k)

index-recommendations format=show-cost
SELECT t1.k, t1.i, t2.i
FROM t1 LEFT JOIN t2
ON t1.k = t2.k
WHERE EXISTS (SELECT * FROM t3 WHERE t3.f > t3.k)
ORDER BY t1.k, t2.i, t1.i DESC
----
index recommendations: 3
1. type: index creation
   SQL command: CREATE INDEX ON t1 (k, i DESC);
2. type: index creation
   SQL command: CREATE INDEX ON t2 (k) STORING (i);
3. type: index creation
   SQL command: CREATE INDEX ON t3 (f) STORING (k, i);
--
Optimal Plan.
sort (segmented)
 ├── columns: k:1(int) i:2(int) i:9(int)
 ├── cost: 2416.02948
 ├── ordering: +1,+9,-2
 └── project
      ├── columns: t1.k:1(int) t1.i:2(int) t2.i:9(int)
      ├── cost: 2263.34815
      ├── ordering: +1
      └── left-join (merge)
           ├── columns: t1.k:1(int) t1.i:2(int) t2.k:8(int) t2.i:9(int)
           ├── left ordering: +1
           ├── right ordering: +8
           ├── cost: 2252.02095
           ├── ordering: +1
           ├── select
           │    ├── columns: t1.k:1(int) t1.i:2(int)
           │    ├── cost: 1122.06355
           │    ├── ordering: +1
           │    ├── scan t1@_hyp_1
           │    │    ├── columns: t1.k:1(int) t1.i:2(int)
           │    │    ├── cost: 1094.72
           │    │    └── ordering: +1
           │    └── filters
           │         └── exists [type=bool, subquery]
           │              └── limit
           │                   ├── columns: t3.k:14(int!null) t3.i:15(int) t3.f:16(float!null)
           │                   ├── cardinality: [0 - 1]
           │                   ├── cost: 17.3135466
           │                   ├── key: ()
           │                   ├── fd: ()-->(14-16)
           │                   ├── select
           │                   │    ├── columns: t3.k:14(int!null) t3.i:15(int) t3.f:16(float!null)
           │                   │    ├── cost: 17.2935466
           │                   │    ├── limit hint: 1.00
           │                   │    ├── scan t3@_hyp_1
           │                   │    │    ├── columns: t3.k:14(int) t3.i:15(int) t3.f:16(float!null)
           │                   │    │    ├── constraint: /16/17: (/NULL - ]
           │                   │    │    ├── cost: 17.2332132
           │                   │    │    └── limit hint: 3.00
           │                   │    └── filters
           │                   │         └── gt [type=bool, outer=(14,16), constraints=(/14: (/NULL - ]; /16: (/NULL - ])]
           │                   │              ├── variable: t3.f:16 [type=float]
           │                   │              └── variable: t3.k:14 [type=int]
           │                   └── const: 1 [type=int]
           ├── select
           │    ├── columns: t2.k:8(int) t2.i:9(int)
           │    ├── cost: 1111.96355
           │    ├── ordering: +8
           │    ├── scan t2@_hyp_2
           │    │    ├── columns: t2.k:8(int) t2.i:9(int)
           │    │    ├── cost: 1084.62
           │    │    └── ordering: +8
           │    └── filters
           │         └── exists [type=bool, subquery]
           │              └── limit
           │                   ├── columns: t3.k:14(int!null) t3.i:15(int) t3.f:16(float!null)
           │                   ├── cardinality: [0 - 1]
           │                   ├── cost: 17.3135466
           │                   ├── key: ()
           │                   ├── fd: ()-->(14-16)
           │                   ├── select
           │                   │    ├── columns: t3.k:14(int!null) t3.i:15(int) t3.f:16(float!null)
           │                   │    ├── cost: 17.2935466
           │                   │    ├── limit hint: 1.00
           │                   │    ├── scan t3@_hyp_1
           │                   │    │    ├── columns: t3.k:14(int) t3.i:15(int) t3.f:16(float!null)
           │                   │    │    ├── constraint: /16/17: (/NULL - ]
           │                   │    │    ├── cost: 17.2332132
           │                   │    │    └── limit hint: 3.00
           │                   │    └── filters
           │                   │         └── gt [type=bool, outer=(14,16), constraints=(/14: (/NULL - ]; /16: (/NULL - ])]
           │                   │              ├── variable: t3.f:16 [type=float]
           │                   │              └── variable: t3.k:14 [type=int]
           │                   └── const: 1 [type=int]
           └── filters (true)