diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c index fb413e7..3085fae 100644 --- a/src/backend/access/nbtree/nbtree.c +++ b/src/backend/access/nbtree/nbtree.c @@ -509,8 +509,8 @@ btrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys, if (orderbys && scan->numberOfOrderBys > 0) memmove(scan->orderByData, - orderbys, - scan->numberOfOrderBys * sizeof(ScanKeyData)); + &orderbys[scan->numberOfOrderBys - 1], + 1 /* scan->numberOfOrderBys */ * sizeof(ScanKeyData)); so->scanDirection = NoMovementScanDirection; so->distanceTypeByVal = true; @@ -1554,13 +1554,15 @@ static bool btmatchorderby(IndexOptInfo *index, List *pathkeys, List *index_clauses, List **orderby_clauses_p, List **orderby_clausecols_p) { - Expr *expr; + Expr *expr = NULL; ListCell *lc; int indexcol; int num_eq_cols = 0; + int nsaops = 0; + int last_saop_ord_col = -1; + bool saops[INDEX_MAX_KEYS] = {0}; - /* only one ORDER BY clause is supported */ - if (list_length(pathkeys) != 1) + if (list_length(pathkeys) < 1) return false; /* @@ -1584,6 +1586,7 @@ btmatchorderby(IndexOptInfo *index, List *pathkeys, List *index_clauses, Expr *clause = rinfo->clause; Oid opno; StrategyNumber strat; + bool is_saop; if (!clause) continue; @@ -1593,6 +1596,18 @@ btmatchorderby(IndexOptInfo *index, List *pathkeys, List *index_clauses, OpExpr *opexpr = (OpExpr *) clause; opno = opexpr->opno; + is_saop = false; + } + else if (IsA(clause, ScalarArrayOpExpr)) + { + ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) clause; + + /* We only accept ANY clauses, not ALL */ + if (!saop->useOr) + continue; + + opno = saop->opno; + is_saop = true; } else { @@ -1603,19 +1618,67 @@ btmatchorderby(IndexOptInfo *index, List *pathkeys, List *index_clauses, /* Check if the operator is btree equality operator. */ strat = get_op_opfamily_strategy(opno, index->opfamily[indexcol]); - if (strat == BTEqualStrategyNumber) - num_eq_cols = indexcol + 1; + if (strat != BTEqualStrategyNumber) + continue; + + if (is_saop && indexcol == num_eq_cols) + { + saops[indexcol] = true; + nsaops++; + } + else if (!is_saop && saops[indexcol]) + { + saops[indexcol] = false; + nsaops--; + } + + num_eq_cols = indexcol + 1; } } - /* - * If there are no equality columns try to match only the first column, - * otherwise try all columns. - */ - indexcol = num_eq_cols ? -1 : 0; + foreach(lc, pathkeys) + { + PathKey *pathkey = lfirst_node(PathKey, lc); + + /* + * If there are no equality columns try to match only the first column, + * otherwise try all columns. + */ + indexcol = num_eq_cols ? -1 : 0; + + if ((expr = match_orderbyop_pathkey(index, pathkey, &indexcol))) + break; /* found order-by-operator pathkey */ + + if (!num_eq_cols) + return false; /* first pathkey is not order-by-operator */ - expr = match_orderbyop_pathkey(index, castNode(PathKey, linitial(pathkeys)), - &indexcol); + indexcol = -1; + + if (!(expr = match_pathkey_to_indexcol(index, pathkey, &indexcol))) + return false; + + if (indexcol >= num_eq_cols) + return false; + + if (saops[indexcol]) + { + saops[indexcol] = false; + nsaops--; + + /* + * ORDER BY column numbers for array ops should go in + * non-decreasing order. + */ + if (indexcol < last_saop_ord_col) + return false; + + last_saop_ord_col = indexcol; + } + /* else: order of equality-restricted columns is arbitrary */ + + *orderby_clauses_p = lappend(*orderby_clauses_p, expr); + *orderby_clausecols_p = lappend_int(*orderby_clausecols_p, -indexcol - 1); + } if (!expr) return false; @@ -1627,6 +1690,19 @@ btmatchorderby(IndexOptInfo *index, List *pathkeys, List *index_clauses, if (indexcol > num_eq_cols) return false; + if (nsaops) + { + int i; + + /* + * Check that all preceding array-op columns are included into + * ORDER BY clause. + */ + for (i = 0; i < indexcol; i++) + if (saops[i]) + return false; + } + /* Return first ORDER BY clause's expression and column. */ *orderby_clauses_p = lappend(*orderby_clauses_p, expr); *orderby_clausecols_p = lappend_int(*orderby_clausecols_p, indexcol); diff --git a/src/backend/access/nbtree/nbtutils.c b/src/backend/access/nbtree/nbtutils.c index 5b9294b..e980351 100644 --- a/src/backend/access/nbtree/nbtutils.c +++ b/src/backend/access/nbtree/nbtutils.c @@ -905,10 +905,6 @@ _bt_preprocess_keys(IndexScanDesc scan) { ScanKey ord = scan->orderByData; - if (scan->numberOfOrderBys > 1) - /* it should not happen, see btmatchorderby() */ - elog(ERROR, "only one btree ordering operator is supported"); - Assert(ord->sk_strategy == BtreeKNNSearchStrategyNumber); /* use bidirectional kNN scan by default */ diff --git a/src/backend/executor/nodeIndexscan.c b/src/backend/executor/nodeIndexscan.c index 805abd2..034e3b8 100644 --- a/src/backend/executor/nodeIndexscan.c +++ b/src/backend/executor/nodeIndexscan.c @@ -1644,6 +1644,27 @@ ExecIndexBuildScanKeys(PlanState *planstate, Relation index, InvalidOid, /* no reg proc for this */ (Datum) 0); /* constant */ } + else if (IsA(clause, Var)) + { + /* indexkey IS NULL or indexkey IS NOT NULL */ + Var *var = (Var *) clause; + + Assert(isorderby); + + if (var->varno != INDEX_VAR) + elog(ERROR, "Var indexqual has wrong key"); + + varattno = var->varattno; + + ScanKeyEntryInitialize(this_scan_key, + SK_ORDER_BY | SK_SEARCHNOTNULL, + varattno, /* attribute number to scan */ + InvalidStrategy, /* no strategy */ + InvalidOid, /* no strategy subtype */ + var->varcollid, /* collation FIXME */ + InvalidOid, /* no reg proc for this */ + (Datum) 0); /* constant */ + } else elog(ERROR, "unsupported indexqual type: %d", (int) nodeTag(clause)); diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index b88044b..a1a36ad 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -2945,6 +2945,62 @@ match_rowcompare_to_indexcol(RestrictInfo *rinfo, return NULL; } +/* + * Try to match pathkey to the specified index column (*indexcol >= 0) or + * to all index columns (*indexcol < 0). + */ +Expr * +match_pathkey_to_indexcol(IndexOptInfo *index, PathKey *pathkey, int *indexcol) +{ + ListCell *lc; + + /* Pathkey must request default sort order for the target opfamily */ + if (pathkey->pk_strategy != BTLessStrategyNumber || + pathkey->pk_nulls_first) + return NULL; + + /* If eclass is volatile, no hope of using an indexscan */ + if (pathkey->pk_eclass->ec_has_volatile) + return NULL; + + /* + * Try to match eclass member expression(s) to index. Note that child + * EC members are considered, but only when they belong to the target + * relation. (Unlike regular members, the same expression could be a + * child member of more than one EC. Therefore, the same index could + * be considered to match more than one pathkey list, which is OK + * here. See also get_eclass_for_sort_expr.) + */ + foreach(lc, pathkey->pk_eclass->ec_members) + { + EquivalenceMember *member = lfirst_node(EquivalenceMember, lc); + Expr *expr = member->em_expr; + + /* No possibility of match if it references other relations */ + if (!bms_equal(member->em_relids, index->rel->relids)) + continue; + + /* If *indexcol is non-negative then try to match only to it */ + if (*indexcol >= 0) + { + if (match_index_to_operand((Node *) expr, *indexcol, index)) + /* don't want to look at remaining members */ + return expr; + } + else /* try to match all columns */ + { + for (*indexcol = 0; *indexcol < index->nkeycolumns; ++*indexcol) + { + if (match_index_to_operand((Node *) expr, *indexcol, index)) + /* don't want to look at remaining members */ + return expr; + } + } + } + + return NULL; +} + /**************************************************************************** * ---- ROUTINES TO CHECK ORDERING OPERATORS ---- ****************************************************************************/ diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 236f506..307af39 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -4555,7 +4555,11 @@ fix_indexqual_clause(PlannerInfo *root, IndexOptInfo *index, int indexcol, */ clause = replace_nestloop_params(root, clause); - if (IsA(clause, OpExpr)) + if (indexcol < 0) + { + clause = fix_indexqual_operand(clause, index, -indexcol - 1); + } + else if (IsA(clause, OpExpr)) { OpExpr *op = (OpExpr *) clause; diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 2e3fab6..2076405 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -5284,10 +5284,12 @@ get_quals_from_indexclauses(List *indexclauses) * index key expression is on the left side of binary clauses. */ Cost -index_other_operands_eval_cost(PlannerInfo *root, List *indexquals) +index_other_operands_eval_cost(PlannerInfo *root, List *indexquals, + List *indexcolnos) { Cost qual_arg_cost = 0; ListCell *lc; + ListCell *indexcolno_lc = indexcolnos ? list_head(indexcolnos) : NULL; foreach(lc, indexquals) { @@ -5302,6 +5304,20 @@ index_other_operands_eval_cost(PlannerInfo *root, List *indexquals) if (IsA(clause, RestrictInfo)) clause = ((RestrictInfo *) clause)->clause; + if (indexcolnos) + { + int indexcol = lfirst_int(indexcolno_lc); + + if (indexcol < 0) + { + /* FIXME */ + qual_arg_cost += index_qual_cost.startup + index_qual_cost.per_tuple; + continue; + } + + indexcolno_lc = lnext(indexcolno_lc); + } + if (IsA(clause, OpExpr)) { OpExpr *op = (OpExpr *) clause; @@ -5331,7 +5347,8 @@ index_other_operands_eval_cost(PlannerInfo *root, List *indexquals) other_operand = NULL; /* keep compiler quiet */ } - cost_qual_eval_node(&index_qual_cost, other_operand, root); + if (other_operand) + cost_qual_eval_node(&index_qual_cost, other_operand, root); qual_arg_cost += index_qual_cost.startup + index_qual_cost.per_tuple; } return qual_arg_cost; @@ -5346,6 +5363,7 @@ genericcostestimate(PlannerInfo *root, IndexOptInfo *index = path->indexinfo; List *indexQuals = get_quals_from_indexclauses(path->indexclauses); List *indexOrderBys = path->indexorderbys; + List *indexOrderByCols = path->indexorderbycols; Cost indexStartupCost; Cost indexTotalCost; Selectivity indexSelectivity; @@ -5509,8 +5527,8 @@ genericcostestimate(PlannerInfo *root, * Detecting that that might be needed seems more expensive than it's * worth, though, considering all the other inaccuracies here ... */ - qual_arg_cost = index_other_operands_eval_cost(root, indexQuals) + - index_other_operands_eval_cost(root, indexOrderBys); + qual_arg_cost = index_other_operands_eval_cost(root, indexQuals, NIL) + + index_other_operands_eval_cost(root, indexOrderBys, indexOrderByCols); qual_op_cost = cpu_operator_cost * (list_length(indexQuals) + list_length(indexOrderBys)); @@ -6629,7 +6647,7 @@ gincostestimate(PlannerInfo *root, IndexPath *path, double loop_count, * Add on index qual eval costs, much as in genericcostestimate. But we * can disregard indexorderbys, since GIN doesn't support those. */ - qual_arg_cost = index_other_operands_eval_cost(root, indexQuals); + qual_arg_cost = index_other_operands_eval_cost(root, indexQuals, NIL); qual_op_cost = cpu_operator_cost * list_length(indexQuals); *indexStartupCost += qual_arg_cost; @@ -6809,7 +6827,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count, * the index costs. We can disregard indexorderbys, since BRIN doesn't * support those. */ - qual_arg_cost = index_other_operands_eval_cost(root, indexQuals); + qual_arg_cost = index_other_operands_eval_cost(root, indexQuals, NIL); /* * Compute the startup cost as the cost to read the whole revmap diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index e9f4f75..6428932 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -79,6 +79,8 @@ extern bool indexcol_is_bool_constant_for_query(IndexOptInfo *index, extern bool match_index_to_operand(Node *operand, int indexcol, IndexOptInfo *index); extern void check_index_predicates(PlannerInfo *root, RelOptInfo *rel); +extern Expr *match_pathkey_to_indexcol(IndexOptInfo *index, PathKey *pathkey, + int *indexcol_p); extern Expr *match_orderbyop_pathkey(IndexOptInfo *index, PathKey *pathkey, int *indexcol_p); extern bool match_orderbyop_pathkeys(IndexOptInfo *index, List *pathkeys, diff --git a/src/include/utils/selfuncs.h b/src/include/utils/selfuncs.h index 0ce2175..636e280 100644 --- a/src/include/utils/selfuncs.h +++ b/src/include/utils/selfuncs.h @@ -189,7 +189,7 @@ extern void estimate_hash_bucket_stats(PlannerInfo *root, extern List *get_quals_from_indexclauses(List *indexclauses); extern Cost index_other_operands_eval_cost(PlannerInfo *root, - List *indexquals); + List *indexquals, List *indexcolnos); extern List *add_predicate_to_index_quals(IndexOptInfo *index, List *indexQuals); extern void genericcostestimate(PlannerInfo *root, IndexPath *path, diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out index 0cefbcc..724890b 100644 --- a/src/test/regress/expected/btree_index.out +++ b/src/test/regress/expected/btree_index.out @@ -876,11 +876,9 @@ ORDER BY tenthous <-> 3500; 120 | 9120 (10 rows) --- IN restriction on the first column is not supported +-- IN restriction on the first column is not supported without 'ORDER BY col1 ASC' EXPLAIN (COSTS OFF) -SELECT thousand, tenthous -FROM tenk1 -WHERE thousand IN (5, 120, 3456, 23) +SELECT thousand, tenthous FROM tenk1 WHERE thousand IN (5, 120, 3456, 23) ORDER BY tenthous <-> 3500; QUERY PLAN --------------------------------------------------------------------- @@ -890,6 +888,63 @@ ORDER BY tenthous <-> 3500; Index Cond: (thousand = ANY ('{5,120,3456,23}'::integer[])) (4 rows) +EXPLAIN (COSTS OFF) +SELECT thousand, tenthous FROM tenk1 WHERE thousand IN (5, 120, 3456, 23) +ORDER BY thousand DESC, tenthous <-> 3500; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Sort Key: thousand DESC, ((tenthous <-> 3500)) + -> Index Only Scan using tenk1_thous_tenthous on tenk1 + Index Cond: (thousand = ANY ('{5,120,3456,23}'::integer[])) +(4 rows) + +EXPLAIN (COSTS OFF) +SELECT thousand, tenthous FROM tenk1 WHERE thousand IN (5, 120, 3456, 23) +ORDER BY thousand, tenthous <-> 3500; + QUERY PLAN +--------------------------------------------------------------- + Index Only Scan using tenk1_thous_tenthous on tenk1 + Index Cond: (thousand = ANY ('{5,120,3456,23}'::integer[])) + Order By: (thousand AND (tenthous <-> 3500)) +(3 rows) + +SELECT thousand, tenthous FROM tenk1 WHERE thousand IN (5, 120, 3456, 23) +ORDER BY thousand, tenthous <-> 3500; + thousand | tenthous +----------+---------- + 5 | 3005 + 5 | 4005 + 5 | 2005 + 5 | 5005 + 5 | 1005 + 5 | 6005 + 5 | 5 + 5 | 7005 + 5 | 8005 + 5 | 9005 + 23 | 3023 + 23 | 4023 + 23 | 2023 + 23 | 5023 + 23 | 1023 + 23 | 6023 + 23 | 23 + 23 | 7023 + 23 | 8023 + 23 | 9023 + 120 | 3120 + 120 | 4120 + 120 | 2120 + 120 | 5120 + 120 | 1120 + 120 | 6120 + 120 | 120 + 120 | 7120 + 120 | 8120 + 120 | 9120 +(30 rows) + -- Test kNN search using 4-column index CREATE INDEX tenk1_knn_idx ON tenk1(ten, hundred, thousand, tenthous); -- Ordering by distance to 3rd column @@ -1122,38 +1177,132 @@ WHERE ten = 3 AND hundred = 43 AND thousand = 643 ORDER BY tenthous <-> 4000; 3 | 43 | 643 | 9643 (10 rows) --- Not supported by tenk1_knn_idx (not all previous columns are eq-restricted) +-- Array ops on non-first columns are not supported EXPLAIN (COSTS OFF) SELECT ten, hundred, thousand, tenthous FROM tenk1 -WHERE hundred = 43 AND thousand = 643 ORDER BY tenthous <-> 4000; - QUERY PLAN ------------------------------------------------- - Index Scan using tenk1_thous_tenthous on tenk1 - Index Cond: (thousand = 643) - Order By: (tenthous <-> 4000) - Filter: (hundred = 43) +WHERE ten IN (3, 4, 5) AND hundred IN (23, 24, 35) AND thousand IN (843, 132, 623, 243) +ORDER BY tenthous <-> 6000; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------- + Sort + Sort Key: ((tenthous <-> 6000)) + -> Index Only Scan using tenk1_knn_idx on tenk1 + Index Cond: ((ten = ANY ('{3,4,5}'::integer[])) AND (hundred = ANY ('{23,24,35}'::integer[])) AND (thousand = ANY ('{843,132,623,243}'::integer[]))) (4 rows) +-- All array columns should be included into ORDER BY EXPLAIN (COSTS OFF) SELECT ten, hundred, thousand, tenthous FROM tenk1 -WHERE ten = 3 AND hundred = 43 ORDER BY tenthous <-> 4000; - QUERY PLAN ----------------------------------------------------- - Sort - Sort Key: ((tenthous <-> 4000)) - -> Index Only Scan using tenk1_knn_idx on tenk1 - Index Cond: ((ten = 3) AND (hundred = 43)) +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY tenthous <-> 6000; + QUERY PLAN +------------------------------------------------------------------- + Index Scan using tenk1_thous_tenthous on tenk1 + Index Cond: ((thousand = 123) AND (tenthous > 2000)) + Order By: (tenthous <-> 6000) + Filter: ((hundred = 23) AND (ten = ANY ('{3,4,5}'::integer[]))) (4 rows) +-- Eq-restricted columns can be omitted from ORDER BY EXPLAIN (COSTS OFF) SELECT ten, hundred, thousand, tenthous FROM tenk1 -WHERE ten = 3 AND thousand = 643 ORDER BY tenthous <-> 4000; - QUERY PLAN ------------------------------------------------- - Index Scan using tenk1_thous_tenthous on tenk1 - Index Cond: (thousand = 643) - Order By: (tenthous <-> 4000) - Filter: (ten = 3) +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, tenthous <-> 6000; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ + Index Only Scan using tenk1_knn_idx on tenk1 + Index Cond: ((ten = ANY ('{3,4,5}'::integer[])) AND (hundred = 23) AND (thousand = 123) AND (tenthous > 2000)) + Order By: (ten AND (tenthous <-> 6000)) +(3 rows) + +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, tenthous <-> 6000; + ten | hundred | thousand | tenthous +-----+---------+----------+---------- + 3 | 23 | 123 | 6123 + 3 | 23 | 123 | 5123 + 3 | 23 | 123 | 7123 + 3 | 23 | 123 | 4123 + 3 | 23 | 123 | 8123 + 3 | 23 | 123 | 3123 + 3 | 23 | 123 | 9123 + 3 | 23 | 123 | 2123 +(8 rows) + +EXPLAIN (COSTS OFF) +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, hundred, tenthous <-> 6000; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ + Index Only Scan using tenk1_knn_idx on tenk1 + Index Cond: ((ten = ANY ('{3,4,5}'::integer[])) AND (hundred = 23) AND (thousand = 123) AND (tenthous > 2000)) + Order By: (ten AND (tenthous <-> 6000)) +(3 rows) + +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, hundred, tenthous <-> 6000; + ten | hundred | thousand | tenthous +-----+---------+----------+---------- + 3 | 23 | 123 | 6123 + 3 | 23 | 123 | 5123 + 3 | 23 | 123 | 7123 + 3 | 23 | 123 | 4123 + 3 | 23 | 123 | 8123 + 3 | 23 | 123 | 3123 + 3 | 23 | 123 | 9123 + 3 | 23 | 123 | 2123 +(8 rows) + +EXPLAIN (COSTS OFF) +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4 ,5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, thousand, tenthous <-> 6000; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ + Index Only Scan using tenk1_knn_idx on tenk1 + Index Cond: ((ten = ANY ('{3,4,5}'::integer[])) AND (hundred = 23) AND (thousand = 123) AND (tenthous > 2000)) + Order By: (ten AND (tenthous <-> 6000)) +(3 rows) + +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, thousand, tenthous <-> 6000; + ten | hundred | thousand | tenthous +-----+---------+----------+---------- + 3 | 23 | 123 | 6123 + 3 | 23 | 123 | 5123 + 3 | 23 | 123 | 7123 + 3 | 23 | 123 | 4123 + 3 | 23 | 123 | 8123 + 3 | 23 | 123 | 3123 + 3 | 23 | 123 | 9123 + 3 | 23 | 123 | 2123 +(8 rows) + +EXPLAIN (COSTS OFF) +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, hundred, thousand, tenthous <-> 6000; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ + Index Only Scan using tenk1_knn_idx on tenk1 + Index Cond: ((ten = ANY ('{3,4,5}'::integer[])) AND (hundred = 23) AND (thousand = 123) AND (tenthous > 2000)) + Order By: (ten AND (tenthous <-> 6000)) +(3 rows) + +-- Extra ORDER BY columns after order-by-op are not supported +EXPLAIN (COSTS OFF) +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4, 5) AND hundred = 23 ORDER BY ten, thousand <-> 6000, tenthous; + QUERY PLAN +----------------------------------------------------------------------------- + Sort + Sort Key: ten, ((thousand <-> 6000)), tenthous + -> Index Only Scan using tenk1_knn_idx on tenk1 + Index Cond: ((ten = ANY ('{3,4,5}'::integer[])) AND (hundred = 23)) (4 rows) DROP INDEX tenk1_knn_idx; diff --git a/src/test/regress/sql/btree_index.sql b/src/test/regress/sql/btree_index.sql index 5e3bffe..69c890e 100644 --- a/src/test/regress/sql/btree_index.sql +++ b/src/test/regress/sql/btree_index.sql @@ -345,13 +345,22 @@ FROM tenk1 WHERE thousand = 120 ORDER BY tenthous <-> 3500; --- IN restriction on the first column is not supported +-- IN restriction on the first column is not supported without 'ORDER BY col1 ASC' EXPLAIN (COSTS OFF) -SELECT thousand, tenthous -FROM tenk1 -WHERE thousand IN (5, 120, 3456, 23) +SELECT thousand, tenthous FROM tenk1 WHERE thousand IN (5, 120, 3456, 23) ORDER BY tenthous <-> 3500; +EXPLAIN (COSTS OFF) +SELECT thousand, tenthous FROM tenk1 WHERE thousand IN (5, 120, 3456, 23) +ORDER BY thousand DESC, tenthous <-> 3500; + +EXPLAIN (COSTS OFF) +SELECT thousand, tenthous FROM tenk1 WHERE thousand IN (5, 120, 3456, 23) +ORDER BY thousand, tenthous <-> 3500; + +SELECT thousand, tenthous FROM tenk1 WHERE thousand IN (5, 120, 3456, 23) +ORDER BY thousand, tenthous <-> 3500; + -- Test kNN search using 4-column index CREATE INDEX tenk1_knn_idx ON tenk1(ten, hundred, thousand, tenthous); @@ -378,18 +387,55 @@ WHERE ten = 3 AND hundred = 43 AND thousand = 643 ORDER BY tenthous <-> 4000; SELECT ten, hundred, thousand, tenthous FROM tenk1 WHERE ten = 3 AND hundred = 43 AND thousand = 643 ORDER BY tenthous <-> 4000; --- Not supported by tenk1_knn_idx (not all previous columns are eq-restricted) +-- Array ops on non-first columns are not supported +EXPLAIN (COSTS OFF) +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4, 5) AND hundred IN (23, 24, 35) AND thousand IN (843, 132, 623, 243) +ORDER BY tenthous <-> 6000; + +-- All array columns should be included into ORDER BY +EXPLAIN (COSTS OFF) +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY tenthous <-> 6000; + +-- Eq-restricted columns can be omitted from ORDER BY EXPLAIN (COSTS OFF) SELECT ten, hundred, thousand, tenthous FROM tenk1 -WHERE hundred = 43 AND thousand = 643 ORDER BY tenthous <-> 4000; +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, tenthous <-> 6000; + +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, tenthous <-> 6000; + +EXPLAIN (COSTS OFF) +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, hundred, tenthous <-> 6000; + +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, hundred, tenthous <-> 6000; + +EXPLAIN (COSTS OFF) +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4 ,5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, thousand, tenthous <-> 6000; + +SELECT ten, hundred, thousand, tenthous FROM tenk1 +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, thousand, tenthous <-> 6000; EXPLAIN (COSTS OFF) SELECT ten, hundred, thousand, tenthous FROM tenk1 -WHERE ten = 3 AND hundred = 43 ORDER BY tenthous <-> 4000; +WHERE ten IN (3, 4, 5) AND hundred = 23 AND thousand = 123 AND tenthous > 2000 +ORDER BY ten, hundred, thousand, tenthous <-> 6000; +-- Extra ORDER BY columns after order-by-op are not supported EXPLAIN (COSTS OFF) SELECT ten, hundred, thousand, tenthous FROM tenk1 -WHERE ten = 3 AND thousand = 643 ORDER BY tenthous <-> 4000; +WHERE ten IN (3, 4, 5) AND hundred = 23 ORDER BY ten, thousand <-> 6000, tenthous; DROP INDEX tenk1_knn_idx;