Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit738c8b7

Browse files
author
Karina Litskevich
committed
[PGPRO-11556] Stabilize array test.
Make this test output more independent of PostgreSQL serverversion and system.
1 parent34619f9 commit738c8b7

File tree

5 files changed

+128
-1850
lines changed

5 files changed

+128
-1850
lines changed

‎expected/array.out

Lines changed: 48 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -7,12 +7,6 @@
77
* array.sql and array_1.sql
88
* --------------------
99
* Test output for 64-bit and 32-bit systems respectively.
10-
*
11-
* --------------------
12-
* array_2.sql and array_3.sql
13-
* --------------------
14-
* Since 6ed83d5fa55c in PostgreSQL 17, the order of rows
15-
* in the output has been changed.
1610
*/
1711
set enable_seqscan=off;
1812
set enable_sort=off;
@@ -859,41 +853,70 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
859853
DROP INDEX idx_array;
860854
/*
861855
* Check ordering using distance operator
856+
*
857+
* The idea of the test:
858+
* We want to check that index scan provides as correct ordering by distance
859+
* operator. File 'data/rum_array.data' contains two arrays that statisfy
860+
* i @> '{23,20}' and have finite distance i <=> '{51}', and a bunch of arrays
861+
* that statisfy i @> '{23,20}' and have infinite distance i <=> '{51}'. When
862+
* ordering by distance the order of this bunch of arrays with infinite
863+
* distance is not determined and may depend of PostgreSQL version and system.
864+
* Adding another sort expression to ORDER BY may cause another plan that
865+
* doesn't use ordering provided by index.
866+
* That's why we use the query you see below. We substitute 'Infinity' distance
867+
* value with -1 because 'Infinity' are printed differently in output in
868+
* different PostgreSQL versions. We substitute arrays that have infinite
869+
* distance with {-1} because their order is undefined and we wnat to determine
870+
* the test output.
862871
*/
863872
CREATE TABLE test_array_order (
864873
i int2[]
865874
);
866875
\copy test_array_order(i) from 'data/rum_array.data';
867876
CREATE INDEX idx_array_order ON test_array_order USING rum (i rum_anyarray_ops);
877+
/* Check that plan of the query uses ordering provided by index scan */
868878
EXPLAIN (COSTS OFF)
869-
SELECT *, i <=> '{51}' from test_array_order WHERE i @> '{23,20}' order by i <=> '{51}';
870-
QUERY PLAN
871-
------------------------------------------------------
872-
Index Scan using idx_array_order on test_array_order
873-
Index Cond: (i @> '{23,20}'::smallint[])
874-
Order By: (i <=> '{51}'::smallint[])
875-
(3 rows)
879+
SELECT
880+
CASE WHEN distance = 'Infinity' THEN '{-1}'
881+
ELSE i
882+
END i,
883+
CASE WHEN distance = 'Infinity' THEN -1
884+
ELSE distance::numeric(18,14)
885+
END distance
886+
FROM
887+
(SELECT *, (i <=> '{51}') AS distance
888+
FROM test_array_order WHERE i @> '{23,20}' ORDER BY distance) t;
889+
QUERY PLAN
890+
------------------------------------------------------------
891+
Subquery Scan on t
892+
-> Index Scan using idx_array_order on test_array_order
893+
Index Cond: (i @> '{23,20}'::smallint[])
894+
Order By: (i <=> '{51}'::smallint[])
895+
(4 rows)
876896

877-
SELECT i,
897+
SELECT
898+
CASE WHEN distance = 'Infinity' THEN '{-1}'
899+
ELSE i
900+
END i,
878901
CASE WHEN distance = 'Infinity' THEN -1
879902
ELSE distance::numeric(18,14)
880903
END distance
881904
FROM
882905
(SELECT *, (i <=> '{51}') AS distance
883-
FROM test_array_order WHERE i @> '{23,20}' ORDER BYi <=> '{51}') t;
906+
FROM test_array_order WHERE i @> '{23,20}' ORDER BYdistance) t;
884907
i | distance
885908
---------------------+------------------
886909
{20,23,51} | 1.73205080756888
887910
{33,51,20,77,23,65} | 2.44948974278318
888-
{23,76,34,23,2,20} | -1
889-
{20,60,45,23,29} | -1
890-
{23,89,38,20,40,95} | -1
891-
{23,20,72} | -1
892-
{73,23,20} | -1
893-
{6,97,20,89,23} | -1
894-
{20,98,30,23,1,66} | -1
895-
{57,23,39,46,50,20} | -1
896-
{81,20,26,22,23} | -1
897-
{18,23,10,90,15,20} | -1
911+
{-1} | -1
912+
{-1} | -1
913+
{-1} | -1
914+
{-1} | -1
915+
{-1} | -1
916+
{-1} | -1
917+
{-1} | -1
918+
{-1} | -1
919+
{-1} | -1
920+
{-1} | -1
898921
(12 rows)
899922

‎expected/array_1.out

Lines changed: 48 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -7,12 +7,6 @@
77
* array.sql and array_1.sql
88
* --------------------
99
* Test output for 64-bit and 32-bit systems respectively.
10-
*
11-
* --------------------
12-
* array_2.sql and array_3.sql
13-
* --------------------
14-
* Since 6ed83d5fa55c in PostgreSQL 17, the order of rows
15-
* in the output has been changed.
1610
*/
1711
set enable_seqscan=off;
1812
set enable_sort=off;
@@ -852,41 +846,70 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
852846
DROP INDEX idx_array;
853847
/*
854848
* Check ordering using distance operator
849+
*
850+
* The idea of the test:
851+
* We want to check that index scan provides as correct ordering by distance
852+
* operator. File 'data/rum_array.data' contains two arrays that statisfy
853+
* i @> '{23,20}' and have finite distance i <=> '{51}', and a bunch of arrays
854+
* that statisfy i @> '{23,20}' and have infinite distance i <=> '{51}'. When
855+
* ordering by distance the order of this bunch of arrays with infinite
856+
* distance is not determined and may depend of PostgreSQL version and system.
857+
* Adding another sort expression to ORDER BY may cause another plan that
858+
* doesn't use ordering provided by index.
859+
* That's why we use the query you see below. We substitute 'Infinity' distance
860+
* value with -1 because 'Infinity' are printed differently in output in
861+
* different PostgreSQL versions. We substitute arrays that have infinite
862+
* distance with {-1} because their order is undefined and we wnat to determine
863+
* the test output.
855864
*/
856865
CREATE TABLE test_array_order (
857866
i int2[]
858867
);
859868
\copy test_array_order(i) from 'data/rum_array.data';
860869
CREATE INDEX idx_array_order ON test_array_order USING rum (i rum_anyarray_ops);
870+
/* Check that plan of the query uses ordering provided by index scan */
861871
EXPLAIN (COSTS OFF)
862-
SELECT *, i <=> '{51}' from test_array_order WHERE i @> '{23,20}' order by i <=> '{51}';
863-
QUERY PLAN
864-
------------------------------------------------------
865-
Index Scan using idx_array_order on test_array_order
866-
Index Cond: (i @> '{23,20}'::smallint[])
867-
Order By: (i <=> '{51}'::smallint[])
868-
(3 rows)
872+
SELECT
873+
CASE WHEN distance = 'Infinity' THEN '{-1}'
874+
ELSE i
875+
END i,
876+
CASE WHEN distance = 'Infinity' THEN -1
877+
ELSE distance::numeric(18,14)
878+
END distance
879+
FROM
880+
(SELECT *, (i <=> '{51}') AS distance
881+
FROM test_array_order WHERE i @> '{23,20}' ORDER BY distance) t;
882+
QUERY PLAN
883+
------------------------------------------------------------
884+
Subquery Scan on t
885+
-> Index Scan using idx_array_order on test_array_order
886+
Index Cond: (i @> '{23,20}'::smallint[])
887+
Order By: (i <=> '{51}'::smallint[])
888+
(4 rows)
869889

870-
SELECT i,
890+
SELECT
891+
CASE WHEN distance = 'Infinity' THEN '{-1}'
892+
ELSE i
893+
END i,
871894
CASE WHEN distance = 'Infinity' THEN -1
872895
ELSE distance::numeric(18,14)
873896
END distance
874897
FROM
875898
(SELECT *, (i <=> '{51}') AS distance
876-
FROM test_array_order WHERE i @> '{23,20}' ORDER BYi <=> '{51}') t;
899+
FROM test_array_order WHERE i @> '{23,20}' ORDER BYdistance) t;
877900
i | distance
878901
---------------------+------------------
879902
{20,23,51} | 1.73205080756888
880903
{33,51,20,77,23,65} | 2.44948974278318
881-
{23,76,34,23,2,20} | -1
882-
{20,60,45,23,29} | -1
883-
{23,89,38,20,40,95} | -1
884-
{23,20,72} | -1
885-
{73,23,20} | -1
886-
{6,97,20,89,23} | -1
887-
{20,98,30,23,1,66} | -1
888-
{57,23,39,46,50,20} | -1
889-
{81,20,26,22,23} | -1
890-
{18,23,10,90,15,20} | -1
904+
{-1} | -1
905+
{-1} | -1
906+
{-1} | -1
907+
{-1} | -1
908+
{-1} | -1
909+
{-1} | -1
910+
{-1} | -1
911+
{-1} | -1
912+
{-1} | -1
913+
{-1} | -1
891914
(12 rows)
892915

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp