This is an automated email from the ASF dual-hosted git repository. oppenheimer01 pushed a commit to branch cbdb-postgres-merge in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 186f926abfebc7ee6b0d69d7693eb4da8df7bd80 Author: Jianghua.yjh <[email protected]> AuthorDate: Thu Apr 16 10:16:19 2026 -0700 ORCA: fall back to Postgres planner for KNN ORDER BY queries (#1653) ORCA is unaware of amcanorderbyop, so it plans "ORDER BY col <-> val" queries with a full Seq Scan + Sort instead of a native KNN ordered index scan. Detect this pattern by checking whether any ORDER BY target is an operator with amoppurpose = AMOP_ORDER in pg_amop and at least one direct Var argument, then raise ExmiQuery2DXLUnsupportedFeature to hand the query off to the Postgres planner, which generates an efficient Index Only Scan with native KNN ordering. Queries where the ordering operator's arguments are entirely computed expressions (e.g. circle(col,1) <-> point(0,0)) are excluded from the fallback to avoid lossy-distance errors in index-only scans. Co-authored-by: reshke <[email protected]> --- contrib/btree_gist/expected/cash_optimizer.out | 11 +- contrib/btree_gist/expected/date_optimizer.out | 7 +- contrib/btree_gist/expected/float4_optimizer.out | 7 +- contrib/btree_gist/expected/float8_optimizer.out | 7 +- contrib/btree_gist/expected/int2_optimizer.out | 7 +- contrib/btree_gist/expected/int4_optimizer.out | 7 +- contrib/btree_gist/expected/int8_optimizer.out | 7 +- contrib/btree_gist/expected/interval_optimizer.out | 24 ++--- contrib/btree_gist/expected/time_optimizer.out | 7 +- .../btree_gist/expected/timestamp_optimizer.out | 7 +- .../btree_gist/expected/timestamptz_optimizer.out | 7 +- contrib/pg_trgm/expected/pg_trgm_optimizer.out | 23 +++-- src/backend/gpopt/gpdbwrappers.cpp | 11 ++ .../gpopt/translate/CTranslatorQueryToDXL.cpp | 9 ++ src/backend/optimizer/util/walkers.c | 112 +++++++++++++++++++++ src/include/gpopt/gpdbwrappers.h | 3 + src/include/optimizer/walkers.h | 1 + .../regress/expected/create_index_optimizer.out | 45 ++++----- src/test/regress/expected/gist_optimizer.out | 63 +++++------- 19 files changed, 242 insertions(+), 123 deletions(-) diff --git a/contrib/btree_gist/expected/cash_optimizer.out b/contrib/btree_gist/expected/cash_optimizer.out index 171dec7e511..f2c9ac07420 100644 --- a/contrib/btree_gist/expected/cash_optimizer.out +++ b/contrib/btree_gist/expected/cash_optimizer.out @@ -77,12 +77,11 @@ SELECT a, a <-> '21472.79' FROM moneytmp ORDER BY a <-> '21472.79' LIMIT 3; QUERY PLAN ------------------------------------------------------------ Limit - -> Sort - Sort Key: ((a <-> '$21,472.79'::money)) - -> Result - -> Gather Motion 3:1 (slice1; segments: 3) - -> Seq Scan on moneytmp - Optimizer: GPORCA + -> Gather Motion 3:1 (slice1; segments: 3) + Merge Key: ((a <-> '$21,472.79'::money)) + -> Limit + -> Index Only Scan using moneyidx on moneytmp + Order By: (a <-> '$21,472.79'::money) (7 rows) SELECT a, a <-> '21472.79' FROM moneytmp ORDER BY a <-> '21472.79' LIMIT 3; diff --git a/contrib/btree_gist/expected/date_optimizer.out b/contrib/btree_gist/expected/date_optimizer.out index a77041f847f..12269cf169b 100644 --- a/contrib/btree_gist/expected/date_optimizer.out +++ b/contrib/btree_gist/expected/date_optimizer.out @@ -79,10 +79,9 @@ SELECT a, a <-> '2001-02-13' FROM datetmp ORDER BY a <-> '2001-02-13' LIMIT 3; Limit -> Gather Motion 3:1 (slice1; segments: 3) Merge Key: ((a <-> '02-13-2001'::date)) - -> Sort - Sort Key: ((a <-> '02-13-2001'::date)) - -> Seq Scan on datetmp - Optimizer: GPORCA + -> Limit + -> Index Only Scan using dateidx on datetmp + Order By: (a <-> '02-13-2001'::date) (7 rows) SELECT a, a <-> '2001-02-13' FROM datetmp ORDER BY a <-> '2001-02-13' LIMIT 3; diff --git a/contrib/btree_gist/expected/float4_optimizer.out b/contrib/btree_gist/expected/float4_optimizer.out index cc40e9bd1ae..7b71a2f5112 100644 --- a/contrib/btree_gist/expected/float4_optimizer.out +++ b/contrib/btree_gist/expected/float4_optimizer.out @@ -79,10 +79,9 @@ SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3; Limit -> Gather Motion 3:1 (slice1; segments: 3) Merge Key: ((a <-> '-179'::real)) - -> Sort - Sort Key: ((a <-> '-179'::real)) - -> Seq Scan on float4tmp - Optimizer: GPORCA + -> Limit + -> Index Only Scan using float4idx on float4tmp + Order By: (a <-> '-179'::real) (7 rows) SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3; diff --git a/contrib/btree_gist/expected/float8_optimizer.out b/contrib/btree_gist/expected/float8_optimizer.out index 1bd96c44d3b..18e5c195286 100644 --- a/contrib/btree_gist/expected/float8_optimizer.out +++ b/contrib/btree_gist/expected/float8_optimizer.out @@ -79,10 +79,9 @@ SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3; Limit -> Gather Motion 3:1 (slice1; segments: 3) Merge Key: ((a <-> '-1890'::double precision)) - -> Sort - Sort Key: ((a <-> '-1890'::double precision)) - -> Seq Scan on float8tmp - Optimizer: GPORCA + -> Limit + -> Index Only Scan using float8idx on float8tmp + Order By: (a <-> '-1890'::double precision) (7 rows) SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3; diff --git a/contrib/btree_gist/expected/int2_optimizer.out b/contrib/btree_gist/expected/int2_optimizer.out index fdfc859097b..f8f6a428b93 100644 --- a/contrib/btree_gist/expected/int2_optimizer.out +++ b/contrib/btree_gist/expected/int2_optimizer.out @@ -79,10 +79,9 @@ SELECT a, a <-> '237' FROM int2tmp ORDER BY a <-> '237' LIMIT 3; Limit -> Gather Motion 3:1 (slice1; segments: 3) Merge Key: ((a <-> '237'::smallint)) - -> Sort - Sort Key: ((a <-> '237'::smallint)) - -> Seq Scan on int2tmp - Optimizer: GPORCA + -> Limit + -> Index Only Scan using int2idx on int2tmp + Order By: (a <-> '237'::smallint) (7 rows) SELECT a, a <-> '237' FROM int2tmp ORDER BY a <-> '237' LIMIT 3; diff --git a/contrib/btree_gist/expected/int4_optimizer.out b/contrib/btree_gist/expected/int4_optimizer.out index 67107e63bfa..6877fb09af5 100644 --- a/contrib/btree_gist/expected/int4_optimizer.out +++ b/contrib/btree_gist/expected/int4_optimizer.out @@ -79,10 +79,9 @@ SELECT a, a <-> '237' FROM int4tmp ORDER BY a <-> '237' LIMIT 3; Limit -> Gather Motion 3:1 (slice1; segments: 3) Merge Key: ((a <-> 237)) - -> Sort - Sort Key: ((a <-> 237)) - -> Seq Scan on int4tmp - Optimizer: GPORCA + -> Limit + -> Index Only Scan using int4idx on int4tmp + Order By: (a <-> 237) (7 rows) SELECT a, a <-> '237' FROM int4tmp ORDER BY a <-> '237' LIMIT 3; diff --git a/contrib/btree_gist/expected/int8_optimizer.out b/contrib/btree_gist/expected/int8_optimizer.out index ba8e21135e8..962dd314661 100644 --- a/contrib/btree_gist/expected/int8_optimizer.out +++ b/contrib/btree_gist/expected/int8_optimizer.out @@ -79,10 +79,9 @@ SELECT a, a <-> '464571291354841' FROM int8tmp ORDER BY a <-> '464571291354841' Limit -> Gather Motion 3:1 (slice1; segments: 3) Merge Key: ((a <-> '464571291354841'::bigint)) - -> Sort - Sort Key: ((a <-> '464571291354841'::bigint)) - -> Seq Scan on int8tmp - Optimizer: GPORCA + -> Limit + -> Index Only Scan using int8idx on int8tmp + Order By: (a <-> '464571291354841'::bigint) (7 rows) SELECT a, a <-> '464571291354841' FROM int8tmp ORDER BY a <-> '464571291354841' LIMIT 3; diff --git a/contrib/btree_gist/expected/interval_optimizer.out b/contrib/btree_gist/expected/interval_optimizer.out index f5afd17456b..f0a4e850aeb 100644 --- a/contrib/btree_gist/expected/interval_optimizer.out +++ b/contrib/btree_gist/expected/interval_optimizer.out @@ -74,15 +74,15 @@ SELECT count(*) FROM intervaltmp WHERE a > '199 days 21:21:23'::interval; EXPLAIN (COSTS OFF) SELECT a, a <-> '199 days 21:21:23' FROM intervaltmp ORDER BY a <-> '199 days 21:21:23' LIMIT 3; - QUERY PLAN ------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------- Limit -> Gather Motion 3:1 (slice1; segments: 3) Merge Key: ((a <-> '@ 199 days 21 hours 21 mins 23 secs'::interval)) - -> Sort - Sort Key: ((a <-> '@ 199 days 21 hours 21 mins 23 secs'::interval)) - -> Seq Scan on intervaltmp - Optimizer: GPORCA + -> Limit + -> Index Only Scan using intervalidx on intervaltmp + Order By: (a <-> '@ 199 days 21 hours 21 mins 23 secs'::interval) + Optimizer: Postgres query optimizer (7 rows) SELECT a, a <-> '199 days 21:21:23' FROM intervaltmp ORDER BY a <-> '199 days 21:21:23' LIMIT 3; @@ -96,15 +96,15 @@ SELECT a, a <-> '199 days 21:21:23' FROM intervaltmp ORDER BY a <-> '199 days 21 SET enable_indexonlyscan=off; EXPLAIN (COSTS OFF) SELECT a, a <-> '199 days 21:21:23' FROM intervaltmp ORDER BY a <-> '199 days 21:21:23' LIMIT 3; - QUERY PLAN ------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------- Limit -> Gather Motion 3:1 (slice1; segments: 3) Merge Key: ((a <-> '@ 199 days 21 hours 21 mins 23 secs'::interval)) - -> Sort - Sort Key: ((a <-> '@ 199 days 21 hours 21 mins 23 secs'::interval)) - -> Seq Scan on intervaltmp - Optimizer: GPORCA + -> Limit + -> Index Scan using intervalidx on intervaltmp + Order By: (a <-> '@ 199 days 21 hours 21 mins 23 secs'::interval) + Optimizer: Postgres query optimizer (7 rows) SELECT a, a <-> '199 days 21:21:23' FROM intervaltmp ORDER BY a <-> '199 days 21:21:23' LIMIT 3; diff --git a/contrib/btree_gist/expected/time_optimizer.out b/contrib/btree_gist/expected/time_optimizer.out index 590ada880b9..40d49e79b02 100644 --- a/contrib/btree_gist/expected/time_optimizer.out +++ b/contrib/btree_gist/expected/time_optimizer.out @@ -79,10 +79,9 @@ SELECT a, a <-> '10:57:11' FROM timetmp ORDER BY a <-> '10:57:11' LIMIT 3; Limit -> Gather Motion 3:1 (slice1; segments: 3) Merge Key: ((a <-> '10:57:11'::time without time zone)) - -> Sort - Sort Key: ((a <-> '10:57:11'::time without time zone)) - -> Seq Scan on timetmp - Optimizer: GPORCA + -> Limit + -> Index Only Scan using timeidx on timetmp + Order By: (a <-> '10:57:11'::time without time zone) (7 rows) SELECT a, a <-> '10:57:11' FROM timetmp ORDER BY a <-> '10:57:11' LIMIT 3; diff --git a/contrib/btree_gist/expected/timestamp_optimizer.out b/contrib/btree_gist/expected/timestamp_optimizer.out index 1b8e709fe90..85c3a1a5e5d 100644 --- a/contrib/btree_gist/expected/timestamp_optimizer.out +++ b/contrib/btree_gist/expected/timestamp_optimizer.out @@ -79,10 +79,9 @@ SELECT a, a <-> '2004-10-26 08:55:08' FROM timestamptmp ORDER BY a <-> '2004-10- Limit -> Gather Motion 3:1 (slice1; segments: 3) Merge Key: ((a <-> 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)) - -> Sort - Sort Key: ((a <-> 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)) - -> Seq Scan on timestamptmp - Optimizer: GPORCA + -> Limit + -> Index Only Scan using timestampidx on timestamptmp + Order By: (a <-> 'Tue Oct 26 08:55:08 2004'::timestamp without time zone) (7 rows) SELECT a, a <-> '2004-10-26 08:55:08' FROM timestamptmp ORDER BY a <-> '2004-10-26 08:55:08' LIMIT 3; diff --git a/contrib/btree_gist/expected/timestamptz_optimizer.out b/contrib/btree_gist/expected/timestamptz_optimizer.out index 2173c5dca35..a9e043f98a6 100644 --- a/contrib/btree_gist/expected/timestamptz_optimizer.out +++ b/contrib/btree_gist/expected/timestamptz_optimizer.out @@ -199,10 +199,9 @@ SELECT a, a <-> '2018-12-18 10:59:54 GMT+2' FROM timestamptztmp ORDER BY a <-> ' Limit -> Gather Motion 3:1 (slice1; segments: 3) Merge Key: ((a <-> 'Tue Dec 18 04:59:54 2018 PST'::timestamp with time zone)) - -> Sort - Sort Key: ((a <-> 'Tue Dec 18 04:59:54 2018 PST'::timestamp with time zone)) - -> Seq Scan on timestamptztmp - Optimizer: GPORCA + -> Limit + -> Index Only Scan using timestamptzidx on timestamptztmp + Order By: (a <-> 'Tue Dec 18 04:59:54 2018 PST'::timestamp with time zone) (7 rows) SELECT a, a <-> '2018-12-18 10:59:54 GMT+2' FROM timestamptztmp ORDER BY a <-> '2018-12-18 10:59:54 GMT+2' LIMIT 3; diff --git a/contrib/pg_trgm/expected/pg_trgm_optimizer.out b/contrib/pg_trgm/expected/pg_trgm_optimizer.out index b40f24add08..8684f352aed 100644 --- a/contrib/pg_trgm/expected/pg_trgm_optimizer.out +++ b/contrib/pg_trgm/expected/pg_trgm_optimizer.out @@ -2351,6 +2351,7 @@ select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988 -> Limit -> Index Scan using trgm_idx on test_trgm Order By: (t <-> 'q0987wertyu0988'::text) + Optimizer: Postgres query optimizer (7 rows) select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2; @@ -5004,8 +5005,8 @@ select * from test2 where t ~ '/\d+/-\d'; -- test = operator explain (costs off) select * from test2 where t = 'abcdef'; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) -> Bitmap Heap Scan on test2 Recheck Cond: (t = 'abcdef'::text) @@ -5021,8 +5022,8 @@ select * from test2 where t = 'abcdef'; explain (costs off) select * from test2 where t = '%line%'; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) -> Bitmap Heap Scan on test2 Recheck Cond: (t = '%line%'::text) @@ -5312,14 +5313,15 @@ select * from test2 where t ~ '/\d+/-\d'; -- test = operator explain (costs off) select * from test2 where t = 'abcdef'; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) -> Bitmap Heap Scan on test2 Recheck Cond: (t = 'abcdef'::text) -> Bitmap Index Scan on test2_idx_gist Index Cond: (t = 'abcdef'::text) -(2 rows) + Optimizer: Postgres query optimizer +(6 rows) select * from test2 where t = 'abcdef'; t @@ -5329,13 +5331,14 @@ select * from test2 where t = 'abcdef'; explain (costs off) select * from test2 where t = '%line%'; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) -> Bitmap Heap Scan on test2 Recheck Cond: (t = '%line%'::text) -> Bitmap Index Scan on test2_idx_gist Index Cond: (t = '%line%'::text) + Optimizer: Postgres query optimizer (6 rows) select * from test2 where t = '%line%'; @@ -5424,7 +5427,7 @@ SELECT DISTINCT city, similarity(city, 'Warsaw'), show_limit() -> Index Scan using restaurants_city_idx on restaurants Index Cond: (city % 'Warsaw'::text) Filter: (city % 'Warsaw'::text) - Optimizer: Pivotal Optimizer (GPORCA) + Optimizer: GPORCA (9 rows) SELECT set_limit(0.3); diff --git a/src/backend/gpopt/gpdbwrappers.cpp b/src/backend/gpopt/gpdbwrappers.cpp index fb24401b941..2b1f3306b4e 100644 --- a/src/backend/gpopt/gpdbwrappers.cpp +++ b/src/backend/gpopt/gpdbwrappers.cpp @@ -2015,6 +2015,17 @@ gpdb::CheckCollation(Node *node) return -1; } +bool +gpdb::HasOrderByOrderingOp(Query *query) +{ + GP_WRAP_START; + { + return has_orderby_ordering_op(query); + } + GP_WRAP_END; + return false; +} + Node * gpdb::CoerceToCommonType(ParseState *pstate, Node *node, Oid target_type, const char *context) diff --git a/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp b/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp index 5c47a0c2c6b..df662af05f5 100644 --- a/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp +++ b/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp @@ -324,6 +324,15 @@ CTranslatorQueryToDXL::CheckUnsupportedNodeTypes(Query *query) GPOS_RAISE(gpdxl::ExmaDXL, gpdxl::ExmiQuery2DXLUnsupportedFeature, GPOS_WSZ_LIT("Non-default collation")); } + + // ORCA does not support amcanorderbyop (KNN ordered index scans). + // Fall back to the PostgreSQL planner for queries whose ORDER BY + // contains an ordering operator (e.g., <-> for distance). + if (gpdb::HasOrderByOrderingOp(query)) + { + GPOS_RAISE(gpdxl::ExmaDXL, gpdxl::ExmiQuery2DXLUnsupportedFeature, + GPOS_WSZ_LIT("ORDER BY with ordering operator (amcanorderbyop)")); + } } //--------------------------------------------------------------------------- diff --git a/src/backend/optimizer/util/walkers.c b/src/backend/optimizer/util/walkers.c index 19ab2322f92..0de8c280e3b 100644 --- a/src/backend/optimizer/util/walkers.c +++ b/src/backend/optimizer/util/walkers.c @@ -8,11 +8,17 @@ #include "postgres.h" +#include "access/htup_details.h" +#include "catalog/pg_amop.h" #include "catalog/pg_collation.h" #include "catalog/pg_type.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" +#include "optimizer/optimizer.h" #include "optimizer/walkers.h" +#include "utils/catcache.h" +#include "utils/lsyscache.h" +#include "utils/syscache.h" /** * Plan node walker related methods. @@ -1014,3 +1020,109 @@ check_collation_walker(Node *node, check_collation_context *context) } } +/* + * is_ordering_op + * + * Return true if the operator is registered as an ordering operator + * (amoppurpose = AMOP_ORDER) in any opfamily in pg_amop. + */ +static bool +is_ordering_op(Oid opno) +{ + CatCList *catlist = SearchSysCacheList1(AMOPOPID, + ObjectIdGetDatum(opno)); + + for (int i = 0; i < catlist->n_members; i++) + { + HeapTuple tp = &catlist->members[i]->tuple; + Form_pg_amop amop = (Form_pg_amop) GETSTRUCT(tp); + + if (amop->amoppurpose == AMOP_ORDER) + { + ReleaseSysCacheList(catlist); + return true; + } + } + ReleaseSysCacheList(catlist); + return false; +} + +/* + * has_plain_var_arg + * + * Return true if the OpExpr has at least one direct Var argument + * (not wrapped in a function or other expression). + * + * Implicit coercions such as RelabelType (binary-compatible casts, e.g. + * varchar -> text) are stripped before the check so that a column + * reference that was implicitly cast to match the operator's input type + * is still recognised as a plain Var. + */ +static bool +has_plain_var_arg(OpExpr *op) +{ + ListCell *arg_lc; + + foreach(arg_lc, op->args) + { + Node *arg = strip_implicit_coercions(lfirst(arg_lc)); + + if (IsA(arg, Var)) + return true; + } + return false; +} + +/* + * has_orderby_ordering_op + * + * Check if the query's ORDER BY uses ordering operators (amoppurpose = + * AMOP_ORDER in pg_amop) that the PostgreSQL planner can safely optimize + * with KNN-GiST index scans but ORCA cannot. + * + * Return true only when ALL ordering-operator expressions in ORDER BY + * have at least one direct Var (column reference) argument. Expressions + * like "circle(p,1) <-> point(0,0)" wrap the column in a function, + * which can cause "lossy distance functions are not supported in + * index-only scans" errors in the planner. In such cases we leave the + * query for ORCA to handle via Seq Scan + Sort. + */ +bool +has_orderby_ordering_op(Query *query) +{ + ListCell *lc; + bool found_ordering_op = false; + + if (query->sortClause == NIL) + return false; + + foreach(lc, query->sortClause) + { + SortGroupClause *sgc = (SortGroupClause *) lfirst(lc); + TargetEntry *tle = get_sortgroupclause_tle(sgc, query->targetList); + Node *expr = (Node *) tle->expr; + + if (!IsA(expr, OpExpr)) + continue; + + OpExpr *opexpr = (OpExpr *) expr; + + if (!is_ordering_op(opexpr->opno)) + continue; + + /* + * Found an ordering operator. Check that at least one argument is + * a plain Var. If any ordering operator has only computed arguments + * (e.g., function calls wrapping columns), bail out immediately — + * falling back to the planner could produce lossy distance errors + * in index-only scans. + */ + found_ordering_op = true; + + if (!has_plain_var_arg(opexpr)) + return false; + } + + return found_ordering_op; +} + diff --git a/src/include/gpopt/gpdbwrappers.h b/src/include/gpopt/gpdbwrappers.h index 3e56e94d743..309647459ef 100644 --- a/src/include/gpopt/gpdbwrappers.h +++ b/src/include/gpopt/gpdbwrappers.h @@ -672,6 +672,9 @@ int FindNodes(Node *node, List *nodeTags); // look for nodes with non-default collation; returns 1 if any exist, -1 otherwise int CheckCollation(Node *node); +// check if ORDER BY uses an ordering operator (amcanorderbyop) unsupported by ORCA +bool HasOrderByOrderingOp(Query *query); + Node *CoerceToCommonType(ParseState *pstate, Node *node, Oid target_type, const char *context); diff --git a/src/include/optimizer/walkers.h b/src/include/optimizer/walkers.h index 6d0d38717f5..d29bc5551e8 100644 --- a/src/include/optimizer/walkers.h +++ b/src/include/optimizer/walkers.h @@ -43,5 +43,6 @@ extern List *extract_nodes_plan(Plan *pl, int nodeTag, bool descendIntoSubquerie extern List *extract_nodes_expression(Node *node, int nodeTag, bool descendIntoSubqueries); extern int find_nodes(Node *node, List *nodeTags); extern int check_collation(Node *node); +extern bool has_orderby_ordering_op(Query *query); #endif /* WALKERS_H_ */ diff --git a/src/test/regress/expected/create_index_optimizer.out b/src/test/regress/expected/create_index_optimizer.out index cffc52a8c3c..355bafc8eaf 100644 --- a/src/test/regress/expected/create_index_optimizer.out +++ b/src/test/regress/expected/create_index_optimizer.out @@ -588,18 +588,16 @@ SELECT * FROM point_tblv WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; --SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; EXPLAIN (COSTS OFF) SELECT * FROM point_tblv WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Result - -> Sort - Sort Key: ((f1 <-> '(0,1)'::point)) - -> Result - -> Gather Motion 3:1 (slice1; segments: 3) - -> Index Scan using gpointind on point_tbl - Index Cond: (f1 <@ '(10,10),(-10,-10)'::box) - Filter: ((f1 <> '(1e-300,-1e-300)'::point) AND ((f1 <-> '(0,0)'::point) <> 'Infinity'::double precision) AND (f1 <@ '(10,10),(-10,-10)'::box)) - Optimizer: Pivotal Optimizer (GPORCA) -(9 rows) + QUERY PLAN +------------------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) + Merge Key: ((point_tbl.f1 <-> '(0,1)'::point)) + -> Index Only Scan using gpointind on point_tbl + Index Cond: (f1 <@ '(10,10),(-10,-10)'::box) + Order By: (f1 <-> '(0,1)'::point) + Filter: ((f1 <> '(1e-300,-1e-300)'::point) AND ((f1 <-> '(0,0)'::point) <> 'Infinity'::double precision)) + Optimizer: Postgres query optimizer +(7 rows) SELECT * FROM point_tblv WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; f1 @@ -703,18 +701,19 @@ SET enable_indexscan = OFF; SET enable_bitmapscan = ON; EXPLAIN (COSTS OFF) SELECT * FROM point_tblv WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Result + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) + Merge Key: ((point_tbl.f1 <-> '(0,1)'::point)) -> Sort - Sort Key: ((f1 <-> '(0,1)'::point)) - -> Result - -> Gather Motion 3:1 (slice1; segments: 3) - -> Index Scan using gpointind on point_tbl - Index Cond: (f1 <@ '(10,10),(-10,-10)'::box) - Filter: ((f1 <> '(1e-300,-1e-300)'::point) AND ((f1 <-> '(0,0)'::point) <> 'Infinity'::double precision) AND (f1 <@ '(10,10),(-10,-10)'::box)) - Optimizer: Pivotal Optimizer (GPORCA) -(9 rows) + Sort Key: ((point_tbl.f1 <-> '(0,1)'::point)) + -> Bitmap Heap Scan on point_tbl + Recheck Cond: (f1 <@ '(10,10),(-10,-10)'::box) + Filter: ((f1 <> '(1e-300,-1e-300)'::point) AND ((f1 <-> '(0,0)'::point) <> 'Infinity'::double precision)) + -> Bitmap Index Scan on gpointind + Index Cond: (f1 <@ '(10,10),(-10,-10)'::box) + Optimizer: Postgres query optimizer +(10 rows) SELECT * FROM point_tblv WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; f1 diff --git a/src/test/regress/expected/gist_optimizer.out b/src/test/regress/expected/gist_optimizer.out index 5062ac383c5..88d19046dd8 100644 --- a/src/test/regress/expected/gist_optimizer.out +++ b/src/test/regress/expected/gist_optimizer.out @@ -98,18 +98,15 @@ select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)); explain (costs off) select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)) order by p <-> point(0.201, 0.201); - QUERY PLAN ---------------------------------------------------------------------- - Result - -> Gather Motion 3:1 (slice1; segments: 3) - Merge Key: ((p <-> '(0.201,0.201)'::point)) - -> Sort - Sort Key: ((p <-> '(0.201,0.201)'::point)) - -> Index Scan using gist_tbl_point_index on gist_tbl - Index Cond: (p <@ '(0.5,0.5),(0,0)'::box) - Filter: (p <@ '(0.5,0.5),(0,0)'::box) - Optimizer: Pivotal Optimizer (GPORCA) version 3.83.0 -(9 rows) + QUERY PLAN +-------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) + Merge Key: ((p <-> '(0.201,0.201)'::point)) + -> Index Only Scan using gist_tbl_point_index on gist_tbl + Index Cond: (p <@ '(0.5,0.5),(0,0)'::box) + Order By: (p <-> '(0.201,0.201)'::point) + Optimizer: Postgres query optimizer +(6 rows) select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)) order by p <-> point(0.201, 0.201); @@ -132,18 +129,15 @@ order by p <-> point(0.201, 0.201); explain (costs off) select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)) order by point(0.101, 0.101) <-> p; - QUERY PLAN ---------------------------------------------------------------------- - Result - -> Gather Motion 3:1 (slice1; segments: 3) - Merge Key: (('(0.101,0.101)'::point <-> p)) - -> Sort - Sort Key: (('(0.101,0.101)'::point <-> p)) - -> Index Scan using gist_tbl_point_index on gist_tbl - Index Cond: (p <@ '(0.5,0.5),(0,0)'::box) - Filter: (p <@ '(0.5,0.5),(0,0)'::box) - Optimizer: Pivotal Optimizer (GPORCA) version 3.83.0 -(9 rows) + QUERY PLAN +-------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) + Merge Key: (('(0.101,0.101)'::point <-> p)) + -> Index Only Scan using gist_tbl_point_index on gist_tbl + Index Cond: (p <@ '(0.5,0.5),(0,0)'::box) + Order By: (p <-> '(0.101,0.101)'::point) + Optimizer: Postgres query optimizer +(6 rows) select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)) order by point(0.101, 0.101) <-> p; @@ -248,18 +242,15 @@ select b from gist_tbl where b <@ box(point(5,5), point(6,6)); explain (costs off) select b from gist_tbl where b <@ box(point(5,5), point(6,6)) order by b <-> point(5.2, 5.91); - QUERY PLAN -------------------------------------------------------------------- - Result - -> Gather Motion 3:1 (slice1; segments: 3) - Merge Key: ((b <-> '(5.2,5.91)'::point)) - -> Sort - Sort Key: ((b <-> '(5.2,5.91)'::point)) - -> Index Scan using gist_tbl_box_index on gist_tbl - Index Cond: (b <@ '(6,6),(5,5)'::box) - Filter: (b <@ '(6,6),(5,5)'::box) - Optimizer: Pivotal Optimizer (GPORCA) -(9 rows) + QUERY PLAN +------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) + Merge Key: ((b <-> '(5.2,5.91)'::point)) + -> Index Only Scan using gist_tbl_box_index on gist_tbl + Index Cond: (b <@ '(6,6),(5,5)'::box) + Order By: (b <-> '(5.2,5.91)'::point) + Optimizer: Postgres query optimizer +(6 rows) select b from gist_tbl where b <@ box(point(5,5), point(6,6)) order by b <-> point(5.2, 5.91); --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
