From 33e92bfd5dd19d731a49312143c5d340530f5ee2 Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas Date: Thu, 21 Dec 2023 17:46:28 +0200 Subject: [PATCH v1 2/5] Show how ORDER BY expression is computed unnecessarily in test In the query in 'create_index', it's unnecessarily to compute the distance, because it is not part of the original SELECT column list. It is added to the target list as a junk column, because the planner uses target list entries to represent ORDER BY columns, but it's not needed in the executor. The junk filter in the executor will remove it from the final result. For simple columns it hardly matters, but if the distince function is expensive it might. The next commits will try to address that; this commit just demonstrates the problem. --- src/test/regress/expected/create_index.out | 13 +++++++------ src/test/regress/expected/gist.out | 15 ++++++++------- src/test/regress/sql/create_index.sql | 2 +- src/test/regress/sql/gist.sql | 2 +- 4 files changed, 17 insertions(+), 15 deletions(-) diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 446cfa678b7..1e2222929f1 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -459,13 +459,14 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; 1 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; - QUERY PLAN ----------------------------------------------- - Index Only Scan using gpointind on point_tbl - Order By: (f1 <-> '(0,1)'::point) -(2 rows) + QUERY PLAN +------------------------------------------------------ + Index Only Scan using gpointind on pg_temp.point_tbl + Output: f1, (f1 <-> '(0,1)'::point) + Order By: (point_tbl.f1 <-> '(0,1)'::point) +(3 rows) SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; f1 diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out index c75bbb23b6e..018b76f21d1 100644 --- a/src/test/regress/expected/gist.out +++ b/src/test/regress/expected/gist.out @@ -75,15 +75,16 @@ select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)); (11 rows) -- Also test an index-only knn-search -explain (costs off) +explain (verbose, 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 --------------------------------------------------------- - 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) -(3 rows) + QUERY PLAN +--------------------------------------------------------------- + Index Only Scan using gist_tbl_point_index on public.gist_tbl + Output: p, (p <-> '(0.201,0.201)'::point) + Index Cond: (gist_tbl.p <@ '(0.5,0.5),(0,0)'::box) + Order By: (gist_tbl.p <-> '(0.201,0.201)'::point) +(4 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); diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index d49ce9f3007..22c08c753a7 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -215,7 +215,7 @@ EXPLAIN (COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; -EXPLAIN (COSTS OFF) +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; diff --git a/src/test/regress/sql/gist.sql b/src/test/regress/sql/gist.sql index 6f1fc65f128..86c111ec500 100644 --- a/src/test/regress/sql/gist.sql +++ b/src/test/regress/sql/gist.sql @@ -66,7 +66,7 @@ select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)); select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)); -- Also test an index-only knn-search -explain (costs off) +explain (verbose, 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); -- 2.39.2