Thanks for feedback
> 2. You can use list_copy_head(root->query_pathkeys,
> list_length(orderbyclauses)); instead of:
>
> + useful_pathkeys = list_truncate(list_copy(root->query_pathkeys),
> + list_length(orderbyclauses));
This code will crash if query_pathkeys is NIL. I need either modify
list_copy_head (v3.1) or add checks before call (v3.2).
I don't know if it's a good idea to modify list_copy_head. It will add
additional overhead to every call.
--
Best regards
Miroslav
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index b8000da56d..880eecd31c 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1001,10 +1001,8 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
match_pathkeys_to_index(index, root->query_pathkeys,
&orderbyclauses,
&orderbyclausecols);
- if (orderbyclauses)
- useful_pathkeys = root->query_pathkeys;
- else
- useful_pathkeys = NIL;
+ useful_pathkeys = list_copy_head(root->query_pathkeys,
+ list_length(orderbyclauses));
}
else
{
@@ -3071,16 +3069,14 @@ expand_indexqual_rowcompare(PlannerInfo *root,
* 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.
+ * On success, the result list is ordered by pathkeys. Result can be shorter
+ * than pathkeys on partial prefix match.
*/
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 */
@@ -3145,8 +3141,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;
}
@@ -3156,12 +3152,9 @@ match_pathkeys_to_index(IndexOptInfo *index, List *pathkeys,
break;
}
- if (!found) /* fail if no match for this pathkey */
+ if (!found) /* end after first not matching expression */
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 0a631124c2..dcf2c0762d 100644
--- a/src/test/regress/expected/incremental_sort.out
+++ b/src/test/regress/expected/incremental_sort.out
@@ -1672,3 +1672,33 @@ order by 1, 2;
-> Function Scan on generate_series
(7 rows)
+-- Incremental sort for not ordered indexes, but with AM order operator
+set enable_incremental_sort = on;
+-- table with GiST index supports closest points retrieval,
+-- but has not sorted index
+create table t (a point, b int);
+create index on t using gist(a);
+insert into t select point(mod(i, 7), mod(i, 11)), i from generate_series(1, 1000) s(i);
+analyze t;
+explain (costs off) select a, b, a <-> point(5, 5) dist from t 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 t_a_idx on t
+ Order By: (a <-> '(5,5)'::point)
+(6 rows)
+
+explain (costs off) select a, b, a <-> point(5, 5) dist from t 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 t_a_idx on t
+ 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 284a354dbb..5cef0ba12c 100644
--- a/src/test/regress/sql/incremental_sort.sql
+++ b/src/test/regress/sql/incremental_sort.sql
@@ -281,3 +281,14 @@ 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;
+
+-- Incremental sort for not ordered indexes, but with AM order operator
+set enable_incremental_sort = on;
+-- table with GiST index supports closest points retrieval,
+-- but has not sorted index
+create table t (a point, b int);
+create index on t using gist(a);
+insert into t select point(mod(i, 7), mod(i, 11)), i from generate_series(1, 1000) s(i);
+analyze t;
+explain (costs off) select a, b, a <-> point(5, 5) dist from t order by dist, b limit 1;
+explain (costs off) select a, b, a <-> point(5, 5) dist from t order by dist, b desc limit 1;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index b8000da56d..fb8e4edf9a 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1001,10 +1001,11 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
match_pathkeys_to_index(index, root->query_pathkeys,
&orderbyclauses,
&orderbyclausecols);
- if (orderbyclauses)
- useful_pathkeys = root->query_pathkeys;
- else
+ if (root->query_pathkeys == NIL)
useful_pathkeys = NIL;
+ else
+ useful_pathkeys = list_copy_head(root->query_pathkeys,
+ list_length(orderbyclauses));
}
else
{
@@ -3071,16 +3072,14 @@ expand_indexqual_rowcompare(PlannerInfo *root,
* 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.
+ * On success, the result list is ordered by pathkeys. Result can be shorter
+ * than pathkeys on partial prefix match.
*/
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 */
@@ -3145,8 +3144,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;
}
@@ -3156,12 +3155,9 @@ match_pathkeys_to_index(IndexOptInfo *index, List *pathkeys,
break;
}
- if (!found) /* fail if no match for this pathkey */
+ if (!found) /* end after first not matching expression */
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 0a631124c2..dcf2c0762d 100644
--- a/src/test/regress/expected/incremental_sort.out
+++ b/src/test/regress/expected/incremental_sort.out
@@ -1672,3 +1672,33 @@ order by 1, 2;
-> Function Scan on generate_series
(7 rows)
+-- Incremental sort for not ordered indexes, but with AM order operator
+set enable_incremental_sort = on;
+-- table with GiST index supports closest points retrieval,
+-- but has not sorted index
+create table t (a point, b int);
+create index on t using gist(a);
+insert into t select point(mod(i, 7), mod(i, 11)), i from generate_series(1, 1000) s(i);
+analyze t;
+explain (costs off) select a, b, a <-> point(5, 5) dist from t 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 t_a_idx on t
+ Order By: (a <-> '(5,5)'::point)
+(6 rows)
+
+explain (costs off) select a, b, a <-> point(5, 5) dist from t 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 t_a_idx on t
+ 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 284a354dbb..5cef0ba12c 100644
--- a/src/test/regress/sql/incremental_sort.sql
+++ b/src/test/regress/sql/incremental_sort.sql
@@ -281,3 +281,14 @@ 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;
+
+-- Incremental sort for not ordered indexes, but with AM order operator
+set enable_incremental_sort = on;
+-- table with GiST index supports closest points retrieval,
+-- but has not sorted index
+create table t (a point, b int);
+create index on t using gist(a);
+insert into t select point(mod(i, 7), mod(i, 11)), i from generate_series(1, 1000) s(i);
+analyze t;
+explain (costs off) select a, b, a <-> point(5, 5) dist from t order by dist, b limit 1;
+explain (costs off) select a, b, a <-> point(5, 5) dist from t order by dist, b desc limit 1;
