diff options
| -rw-r--r-- | src/backend/optimizer/path/indxpath.c | 53 | ||||
| -rw-r--r-- | src/test/regress/expected/incremental_sort.out | 33 | ||||
| -rw-r--r-- | src/test/regress/sql/incremental_sort.sql | 16 |
3 files changed, 77 insertions, 25 deletions
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index 0065c8992bd..6a93d767a5c 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -974,14 +974,20 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel, } else if (index->amcanorderbyop && pathkeys_possibly_useful) { - /* see if we can generate ordering operators for query_pathkeys */ + /* + * See if we can generate ordering operators for query_pathkeys or at + * least some prefix thereof. Matching to just a prefix of the + * query_pathkeys will allow an incremental sort to be considered on + * the index's partially sorted results. + */ match_pathkeys_to_index(index, root->query_pathkeys, &orderbyclauses, &orderbyclausecols); - if (orderbyclauses) + if (list_length(root->query_pathkeys) == list_length(orderbyclauses)) useful_pathkeys = root->query_pathkeys; else - useful_pathkeys = NIL; + useful_pathkeys = list_copy_head(root->query_pathkeys, + list_length(orderbyclauses)); } else { @@ -3051,24 +3057,24 @@ expand_indexqual_rowcompare(PlannerInfo *root, /* * match_pathkeys_to_index - * Test whether an index can produce output ordered according to the - * given pathkeys using "ordering operators". - * - * If it can, return a list of suitable ORDER BY expressions, each of the form - * "indexedcol operator pseudoconstant", along with an integer list of the - * index column numbers (zero based) that each clause would be used with. - * NIL lists are returned if the ordering is not achievable this way. - * - * On success, the result list is ordered by pathkeys, and in fact is - * one-to-one with the requested pathkeys. + * For the given 'index' and 'pathkeys', output a list of suitable ORDER + * BY expressions, each of the form "indexedcol operator pseudoconstant", + * along with an integer list of the index column numbers (zero based) + * that each clause would be used with. + * + * This attempts to find an ORDER BY and index column number for all items in + * the pathkey list, however, if we're unable to match any given pathkey to an + * index column, we return just the ones matched by the function so far. This + * allows callers who are interested in partial matches to get them. Callers + * can determine a partial match vs a full match by checking the outputted + * list lengths. A full match will have one item in the output lists for each + * item in the given 'pathkeys' list. */ static void match_pathkeys_to_index(IndexOptInfo *index, List *pathkeys, List **orderby_clauses_p, List **clause_columns_p) { - List *orderby_clauses = NIL; - List *clause_columns = NIL; ListCell *lc1; *orderby_clauses_p = NIL; /* set default results */ @@ -3084,10 +3090,6 @@ match_pathkeys_to_index(IndexOptInfo *index, List *pathkeys, bool found = false; ListCell *lc2; - /* - * Note: for any failure to match, we just return NIL immediately. - * There is no value in matching just some of the pathkeys. - */ /* Pathkey must request default sort order for the target opfamily */ if (pathkey->pk_strategy != BTLessStrategyNumber || @@ -3133,8 +3135,8 @@ match_pathkeys_to_index(IndexOptInfo *index, List *pathkeys, pathkey->pk_opfamily); if (expr) { - orderby_clauses = lappend(orderby_clauses, expr); - clause_columns = lappend_int(clause_columns, indexcol); + *orderby_clauses_p = lappend(*orderby_clauses_p, expr); + *clause_columns_p = lappend_int(*clause_columns_p, indexcol); found = true; break; } @@ -3144,12 +3146,13 @@ match_pathkeys_to_index(IndexOptInfo *index, List *pathkeys, break; } - if (!found) /* fail if no match for this pathkey */ + /* + * Return the matches found so far when this pathkey couldn't be + * matched to the index. + */ + if (!found) return; } - - *orderby_clauses_p = orderby_clauses; /* success! */ - *clause_columns_p = clause_columns; } /* diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out index 0c3433f8e58..7fdb685313d 100644 --- a/src/test/regress/expected/incremental_sort.out +++ b/src/test/regress/expected/incremental_sort.out @@ -1660,3 +1660,36 @@ order by 1, 2; -> Function Scan on generate_series (7 rows) +reset enable_hashagg; +reset enable_seqscan; +reset enable_incremental_sort; +reset parallel_tuple_cost; +reset parallel_setup_cost; +reset min_parallel_table_scan_size; +reset min_parallel_index_scan_size; +-- Ensure incremental sorts work for amcanorderbyop type indexes +create table point_table (a point, b int); +create index point_table_a_idx on point_table using gist(a); +-- Ensure we get an incremental sort plan for both of the following queries +explain (costs off) select a, b, a <-> point(5, 5) dist from point_table order by dist, b limit 1; + QUERY PLAN +--------------------------------------------------------------- + Limit + -> Incremental Sort + Sort Key: ((a <-> '(5,5)'::point)), b + Presorted Key: ((a <-> '(5,5)'::point)) + -> Index Scan using point_table_a_idx on point_table + Order By: (a <-> '(5,5)'::point) +(6 rows) + +explain (costs off) select a, b, a <-> point(5, 5) dist from point_table order by dist, b desc limit 1; + QUERY PLAN +--------------------------------------------------------------- + Limit + -> Incremental Sort + Sort Key: ((a <-> '(5,5)'::point)), b DESC + Presorted Key: ((a <-> '(5,5)'::point)) + -> Index Scan using point_table_a_idx on point_table + Order By: (a <-> '(5,5)'::point) +(6 rows) + diff --git a/src/test/regress/sql/incremental_sort.sql b/src/test/regress/sql/incremental_sort.sql index 071f8a5268e..ab471bdfffc 100644 --- a/src/test/regress/sql/incremental_sort.sql +++ b/src/test/regress/sql/incremental_sort.sql @@ -276,3 +276,19 @@ from tenk1, lateral (select tenk1.unique1 from generate_series(1, 1000)) as sub; explain (costs off) select sub.unique1, stringu1 || random()::text from tenk1, lateral (select tenk1.unique1 from generate_series(1, 1000)) as sub order by 1, 2; + +reset enable_hashagg; +reset enable_seqscan; +reset enable_incremental_sort; +reset parallel_tuple_cost; +reset parallel_setup_cost; +reset min_parallel_table_scan_size; +reset min_parallel_index_scan_size; + +-- Ensure incremental sorts work for amcanorderbyop type indexes +create table point_table (a point, b int); +create index point_table_a_idx on point_table using gist(a); + +-- Ensure we get an incremental sort plan for both of the following queries +explain (costs off) select a, b, a <-> point(5, 5) dist from point_table order by dist, b limit 1; +explain (costs off) select a, b, a <-> point(5, 5) dist from point_table order by dist, b desc limit 1; |