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

Commit5874c70

Browse files
committed
Speed up sort-order-comparison tests in create_index_spgist.
This test script verifies that KNN searches of an SP-GiST indexproduce the same sort order as a seqscan-and-sort. The FULL JOINsused for that are exceedingly slow, however. Investigation showsthat the problem is that the initial join is on the rank() values,and we have a lot of duplicates due to the data set containing 1000duplicate points. We're therefore going to produce 1000000 joinrows that have to be thrown away again by the join filter.We can improve matters by using row_number() instead of rank(),so that the initial join keys are unique. The catch is thatthat makes the results sensitive to the sorting of rows withequal distances from the reference point. That doesn't matterfor the actually-equal points, but as luck would have it, thedata set also contains two distinct points that have identicaldistances to the origin. So those two rows could legitimatelyappear in either order, causing unwanted output from the checkqueries.However, it doesn't seem like it's the job of this test tocheck whether the <-> operator correctly computes distances;its charter is just to verify that SP-GiST emits the valuesin distance order. So we can dodge the indeterminacy problemby having the check only compare row numbers and distancesnot the actual point values.This change reduces the run time of create_index_spgist by a goodthree-quarters, on my machine, with ensuing beneficial effects onthe runtime of create_index (thanks to interactions with CREATEINDEX CONCURRENTLY tests in the latter). I see a net improvementof more than 2X in the runtime of their parallel test group.Discussion:https://postgr.es/m/735.1554935715@sss.pgh.pa.us
1 parent385d396 commit5874c70

File tree

2 files changed

+48
-60
lines changed

2 files changed

+48
-60
lines changed

‎src/test/regress/expected/create_index_spgist.out

Lines changed: 24 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -81,13 +81,13 @@ SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
8181
(1 row)
8282

8383
CREATE TEMP TABLE quad_point_tbl_ord_seq1 AS
84-
SELECTrank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
84+
SELECTrow_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
8585
FROM quad_point_tbl;
8686
CREATE TEMP TABLE quad_point_tbl_ord_seq2 AS
87-
SELECTrank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
87+
SELECTrow_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
8888
FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
8989
CREATE TEMP TABLE quad_point_tbl_ord_seq3 AS
90-
SELECTrank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
90+
SELECTrow_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
9191
FROM quad_point_tbl WHERE p IS NOT NULL;
9292
SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
9393
count
@@ -327,7 +327,7 @@ SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
327327
(1 row)
328328

329329
EXPLAIN (COSTS OFF)
330-
SELECTrank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
330+
SELECTrow_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
331331
FROM quad_point_tbl;
332332
QUERY PLAN
333333
-----------------------------------------------------------
@@ -337,18 +337,17 @@ FROM quad_point_tbl;
337337
(3 rows)
338338

339339
CREATE TEMP TABLE quad_point_tbl_ord_idx1 AS
340-
SELECTrank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
340+
SELECTrow_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
341341
FROM quad_point_tbl;
342342
SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN quad_point_tbl_ord_idx1 idx
343343
ON seq.n = idx.n
344-
AND (seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
345-
WHERE seq.n IS NULL OR idx.n IS NULL;
344+
WHERE seq.dist IS DISTINCT FROM idx.dist;
346345
n | dist | p | n | dist | p
347346
---+------+---+---+------+---
348347
(0 rows)
349348

350349
EXPLAIN (COSTS OFF)
351-
SELECTrank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
350+
SELECTrow_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
352351
FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
353352
QUERY PLAN
354353
-----------------------------------------------------------
@@ -359,18 +358,17 @@ FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
359358
(4 rows)
360359

361360
CREATE TEMP TABLE quad_point_tbl_ord_idx2 AS
362-
SELECTrank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
361+
SELECTrow_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
363362
FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
364363
SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN quad_point_tbl_ord_idx2 idx
365364
ON seq.n = idx.n
366-
AND (seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
367-
WHERE seq.n IS NULL OR idx.n IS NULL;
365+
WHERE seq.dist IS DISTINCT FROM idx.dist;
368366
n | dist | p | n | dist | p
369367
---+------+---+---+------+---
370368
(0 rows)
371369

372370
EXPLAIN (COSTS OFF)
373-
SELECTrank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
371+
SELECTrow_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
374372
FROM quad_point_tbl WHERE p IS NOT NULL;
375373
QUERY PLAN
376374
-----------------------------------------------------------
@@ -381,12 +379,11 @@ FROM quad_point_tbl WHERE p IS NOT NULL;
381379
(4 rows)
382380

383381
CREATE TEMP TABLE quad_point_tbl_ord_idx3 AS
384-
SELECTrank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
382+
SELECTrow_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
385383
FROM quad_point_tbl WHERE p IS NOT NULL;
386384
SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN quad_point_tbl_ord_idx3 idx
387385
ON seq.n = idx.n
388-
AND (seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
389-
WHERE seq.n IS NULL OR idx.n IS NULL;
386+
WHERE seq.dist IS DISTINCT FROM idx.dist;
390387
n | dist | p | n | dist | p
391388
---+------+---+---+------+---
392389
(0 rows)
@@ -497,7 +494,7 @@ SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
497494
(1 row)
498495

499496
EXPLAIN (COSTS OFF)
500-
SELECTrank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
497+
SELECTrow_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
501498
FROM kd_point_tbl;
502499
QUERY PLAN
503500
-------------------------------------------------------
@@ -507,18 +504,17 @@ FROM kd_point_tbl;
507504
(3 rows)
508505

509506
CREATE TEMP TABLE kd_point_tbl_ord_idx1 AS
510-
SELECTrank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
507+
SELECTrow_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
511508
FROM kd_point_tbl;
512509
SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN kd_point_tbl_ord_idx1 idx
513-
ON seq.n = idx.n AND
514-
(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
515-
WHERE seq.n IS NULL OR idx.n IS NULL;
510+
ON seq.n = idx.n
511+
WHERE seq.dist IS DISTINCT FROM idx.dist;
516512
n | dist | p | n | dist | p
517513
---+------+---+---+------+---
518514
(0 rows)
519515

520516
EXPLAIN (COSTS OFF)
521-
SELECTrank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
517+
SELECTrow_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
522518
FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
523519
QUERY PLAN
524520
---------------------------------------------------------
@@ -529,18 +525,17 @@ FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
529525
(4 rows)
530526

531527
CREATE TEMP TABLE kd_point_tbl_ord_idx2 AS
532-
SELECTrank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
528+
SELECTrow_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
533529
FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
534530
SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN kd_point_tbl_ord_idx2 idx
535-
ON seq.n = idx.n AND
536-
(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
537-
WHERE seq.n IS NULL OR idx.n IS NULL;
531+
ON seq.n = idx.n
532+
WHERE seq.dist IS DISTINCT FROM idx.dist;
538533
n | dist | p | n | dist | p
539534
---+------+---+---+------+---
540535
(0 rows)
541536

542537
EXPLAIN (COSTS OFF)
543-
SELECTrank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
538+
SELECTrow_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
544539
FROM kd_point_tbl WHERE p IS NOT NULL;
545540
QUERY PLAN
546541
-------------------------------------------------------
@@ -551,12 +546,11 @@ FROM kd_point_tbl WHERE p IS NOT NULL;
551546
(4 rows)
552547

553548
CREATE TEMP TABLE kd_point_tbl_ord_idx3 AS
554-
SELECTrank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
549+
SELECTrow_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
555550
FROM kd_point_tbl WHERE p IS NOT NULL;
556551
SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN kd_point_tbl_ord_idx3 idx
557-
ON seq.n = idx.n AND
558-
(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
559-
WHERE seq.n IS NULL OR idx.n IS NULL;
552+
ON seq.n = idx.n
553+
WHERE seq.dist IS DISTINCT FROM idx.dist;
560554
n | dist | p | n | dist | p
561555
---+------+---+---+------+---
562556
(0 rows)

‎src/test/regress/sql/create_index_spgist.sql

Lines changed: 24 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -53,15 +53,15 @@ SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)';
5353
SELECTcount(*)FROM quad_point_tblWHERE p ~='(4585, 365)';
5454

5555
CREATE TEMP TABLE quad_point_tbl_ord_seq1AS
56-
SELECTrank() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
56+
SELECTrow_number() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
5757
FROM quad_point_tbl;
5858

5959
CREATE TEMP TABLE quad_point_tbl_ord_seq2AS
60-
SELECTrank() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
60+
SELECTrow_number() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
6161
FROM quad_point_tblWHERE p<@box'(200,200,1000,1000)';
6262

6363
CREATE TEMP TABLE quad_point_tbl_ord_seq3AS
64-
SELECTrank() OVER (ORDER BY p<->'333,400') n, p<->'333,400' dist, p
64+
SELECTrow_number() OVER (ORDER BY p<->'333,400') n, p<->'333,400' dist, p
6565
FROM quad_point_tblWHERE pIS NOT NULL;
6666

6767
SELECTcount(*)FROM radix_text_tblWHERE t='P0123456789abcdef';
@@ -138,37 +138,34 @@ SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
138138
SELECTcount(*)FROM quad_point_tblWHERE p ~='(4585, 365)';
139139

140140
EXPLAIN (COSTS OFF)
141-
SELECTrank() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
141+
SELECTrow_number() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
142142
FROM quad_point_tbl;
143143
CREATE TEMP TABLE quad_point_tbl_ord_idx1AS
144-
SELECTrank() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
144+
SELECTrow_number() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
145145
FROM quad_point_tbl;
146146
SELECT*FROM quad_point_tbl_ord_seq1 seq FULLJOIN quad_point_tbl_ord_idx1 idx
147147
ONseq.n=idx.n
148-
AND (seq.dist=idx.distANDseq.p ~=idx.pORseq.p ISNULLANDidx.p ISNULL)
149-
WHEREseq.n ISNULLORidx.n ISNULL;
148+
WHEREseq.dist IS DISTINCTFROMidx.dist;
150149

151150
EXPLAIN (COSTS OFF)
152-
SELECTrank() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
151+
SELECTrow_number() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
153152
FROM quad_point_tblWHERE p<@box'(200,200,1000,1000)';
154153
CREATE TEMP TABLE quad_point_tbl_ord_idx2AS
155-
SELECTrank() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
154+
SELECTrow_number() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
156155
FROM quad_point_tblWHERE p<@box'(200,200,1000,1000)';
157156
SELECT*FROM quad_point_tbl_ord_seq2 seq FULLJOIN quad_point_tbl_ord_idx2 idx
158157
ONseq.n=idx.n
159-
AND (seq.dist=idx.distANDseq.p ~=idx.pORseq.p ISNULLANDidx.p ISNULL)
160-
WHEREseq.n ISNULLORidx.n ISNULL;
158+
WHEREseq.dist IS DISTINCTFROMidx.dist;
161159

162160
EXPLAIN (COSTS OFF)
163-
SELECTrank() OVER (ORDER BY p<->'333,400') n, p<->'333,400' dist, p
161+
SELECTrow_number() OVER (ORDER BY p<->'333,400') n, p<->'333,400' dist, p
164162
FROM quad_point_tblWHERE pIS NOT NULL;
165163
CREATE TEMP TABLE quad_point_tbl_ord_idx3AS
166-
SELECTrank() OVER (ORDER BY p<->'333,400') n, p<->'333,400' dist, p
164+
SELECTrow_number() OVER (ORDER BY p<->'333,400') n, p<->'333,400' dist, p
167165
FROM quad_point_tblWHERE pIS NOT NULL;
168166
SELECT*FROM quad_point_tbl_ord_seq3 seq FULLJOIN quad_point_tbl_ord_idx3 idx
169167
ONseq.n=idx.n
170-
AND (seq.dist=idx.distANDseq.p ~=idx.pORseq.p ISNULLANDidx.p ISNULL)
171-
WHEREseq.n ISNULLORidx.n ISNULL;
168+
WHEREseq.dist IS DISTINCTFROMidx.dist;
172169

173170
EXPLAIN (COSTS OFF)
174171
SELECTcount(*)FROM kd_point_tblWHERE p<@box'(200,200,1000,1000)';
@@ -199,37 +196,34 @@ SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
199196
SELECTcount(*)FROM kd_point_tblWHERE p ~='(4585, 365)';
200197

201198
EXPLAIN (COSTS OFF)
202-
SELECTrank() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
199+
SELECTrow_number() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
203200
FROM kd_point_tbl;
204201
CREATE TEMP TABLE kd_point_tbl_ord_idx1AS
205-
SELECTrank() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
202+
SELECTrow_number() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
206203
FROM kd_point_tbl;
207204
SELECT*FROM quad_point_tbl_ord_seq1 seq FULLJOIN kd_point_tbl_ord_idx1 idx
208-
ONseq.n=idx.nAND
209-
(seq.dist=idx.distANDseq.p ~=idx.pORseq.p ISNULLANDidx.p ISNULL)
210-
WHEREseq.n ISNULLORidx.n ISNULL;
205+
ONseq.n=idx.n
206+
WHEREseq.dist IS DISTINCTFROMidx.dist;
211207

212208
EXPLAIN (COSTS OFF)
213-
SELECTrank() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
209+
SELECTrow_number() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
214210
FROM kd_point_tblWHERE p<@box'(200,200,1000,1000)';
215211
CREATE TEMP TABLE kd_point_tbl_ord_idx2AS
216-
SELECTrank() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
212+
SELECTrow_number() OVER (ORDER BY p<->'0,0') n, p<->'0,0' dist, p
217213
FROM kd_point_tblWHERE p<@box'(200,200,1000,1000)';
218214
SELECT*FROM quad_point_tbl_ord_seq2 seq FULLJOIN kd_point_tbl_ord_idx2 idx
219-
ONseq.n=idx.nAND
220-
(seq.dist=idx.distANDseq.p ~=idx.pORseq.p ISNULLANDidx.p ISNULL)
221-
WHEREseq.n ISNULLORidx.n ISNULL;
215+
ONseq.n=idx.n
216+
WHEREseq.dist IS DISTINCTFROMidx.dist;
222217

223218
EXPLAIN (COSTS OFF)
224-
SELECTrank() OVER (ORDER BY p<->'333,400') n, p<->'333,400' dist, p
219+
SELECTrow_number() OVER (ORDER BY p<->'333,400') n, p<->'333,400' dist, p
225220
FROM kd_point_tblWHERE pIS NOT NULL;
226221
CREATE TEMP TABLE kd_point_tbl_ord_idx3AS
227-
SELECTrank() OVER (ORDER BY p<->'333,400') n, p<->'333,400' dist, p
222+
SELECTrow_number() OVER (ORDER BY p<->'333,400') n, p<->'333,400' dist, p
228223
FROM kd_point_tblWHERE pIS NOT NULL;
229224
SELECT*FROM quad_point_tbl_ord_seq3 seq FULLJOIN kd_point_tbl_ord_idx3 idx
230-
ONseq.n=idx.nAND
231-
(seq.dist=idx.distANDseq.p ~=idx.pORseq.p ISNULLANDidx.p ISNULL)
232-
WHEREseq.n ISNULLORidx.n ISNULL;
225+
ONseq.n=idx.n
226+
WHEREseq.dist IS DISTINCTFROMidx.dist;
233227

234228
EXPLAIN (COSTS OFF)
235229
SELECTcount(*)FROM radix_text_tblWHERE t='P0123456789abcdef';

0 commit comments

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