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

Commit614119d

Browse files
committed
Fix intarray's GiST opclasses to not fail for empty arrays with <@.
contrib/intarray considers "arraycol <@ constant-array" to be indexable,but its GiST opclass code fails to reliably find index entries for emptyarray values (which of course should trivially match such queries).This is because the test condition to see whether we should descendthrough a non-leaf node is wrong.Unfortunately, empty array entries could be anywhere in the index,as these index opclasses are currently designed. So there's no wayto fix this except by lobotomizing <@ indexscans to scan the wholeindex ... which is what this patch does. That's pretty unfortunate:the performance is now actually worse than a seqscan, in most cases.We'd be better off to remove <@ from the GiST opclasses entirely,and perhaps a future non-back-patchable patch will do so.In the meantime, applications whose performance is adversely impactedhave a couple of options. They could switch to a GIN index, whichdoesn't have this bug, or they could replace "arraycol <@ constant-array"with "arraycol <@ constant-array AND arraycol && constant-array".That will provide about the same performance as before, and it will findall non-empty subsets of the given constant-array, which is all thatcould reliably be expected of the query before.While at it, add some more regression test cases to improve codecoverage of contrib/intarray.In passing, adjust resize_intArrayType so that when it's returning anempty array, it uses construct_empty_array for that rather thancowboy hacking on the input array. While the hack produces an arraythat looks valid for most purposes, it isn't bitwise equal to emptyarrays produced by other code paths, which could have subtle oddeffects. I don't think this code path is performance-criticalenough to justify such shortcuts. (Back-patch this part only as faras v11; before commit01783ac we were not careful about this inother intarray code paths either.)Back-patch the <@ fixes to all supported versions, since this wasbroken from day one.Patch by me; thanks to Alexander Korotkov for review.Discussion:https://postgr.es/m/458.1565114141@sss.pgh.pa.us
1 parent246893d commit614119d

File tree

4 files changed

+132
-3
lines changed

4 files changed

+132
-3
lines changed

‎contrib/intarray/_int_gist.c

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -94,8 +94,13 @@ g_int_consistent(PG_FUNCTION_ARGS)
9494
retval=inner_int_contains(query,
9595
(ArrayType*)DatumGetPointer(entry->key));
9696
else
97-
retval=inner_int_overlap((ArrayType*)DatumGetPointer(entry->key),
98-
query);
97+
{
98+
/*
99+
* Unfortunately, because empty arrays could be anywhere in
100+
* the index, we must search the whole tree.
101+
*/
102+
retval= true;
103+
}
99104
break;
100105
default:
101106
retval= FALSE;

‎contrib/intarray/_intbig_gist.c

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -591,7 +591,13 @@ g_intbig_consistent(PG_FUNCTION_ARGS)
591591
}
592592
}
593593
else
594-
retval=_intbig_overlap((GISTTYPE*)DatumGetPointer(entry->key),query);
594+
{
595+
/*
596+
* Unfortunately, because empty arrays could be anywhere in
597+
* the index, we must search the whole tree.
598+
*/
599+
retval= true;
600+
}
595601
break;
596602
default:
597603
retval= FALSE;

‎contrib/intarray/expected/_int.out

Lines changed: 98 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -398,6 +398,18 @@ SELECT count(*) from test__int WHERE a @> '{20,23}';
398398
12
399399
(1 row)
400400

401+
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
402+
count
403+
-------
404+
10
405+
(1 row)
406+
407+
SELECT count(*) from test__int WHERE a = '{73,23,20}';
408+
count
409+
-------
410+
1
411+
(1 row)
412+
401413
SELECT count(*) from test__int WHERE a @@ '50&68';
402414
count
403415
-------
@@ -416,6 +428,19 @@ SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
416428
21
417429
(1 row)
418430

431+
SELECT count(*) from test__int WHERE a @@ '20 | !21';
432+
count
433+
-------
434+
6566
435+
(1 row)
436+
437+
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
438+
count
439+
-------
440+
6343
441+
(1 row)
442+
443+
SET enable_seqscan = off; -- not all of these would use index by default
419444
CREATE INDEX text_idx on test__int using gist ( a gist__int_ops );
420445
SELECT count(*) from test__int WHERE a && '{23,50}';
421446
count
@@ -447,6 +472,18 @@ SELECT count(*) from test__int WHERE a @> '{20,23}';
447472
12
448473
(1 row)
449474

475+
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
476+
count
477+
-------
478+
10
479+
(1 row)
480+
481+
SELECT count(*) from test__int WHERE a = '{73,23,20}';
482+
count
483+
-------
484+
1
485+
(1 row)
486+
450487
SELECT count(*) from test__int WHERE a @@ '50&68';
451488
count
452489
-------
@@ -465,6 +502,18 @@ SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
465502
21
466503
(1 row)
467504

505+
SELECT count(*) from test__int WHERE a @@ '20 | !21';
506+
count
507+
-------
508+
6566
509+
(1 row)
510+
511+
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
512+
count
513+
-------
514+
6343
515+
(1 row)
516+
468517
DROP INDEX text_idx;
469518
CREATE INDEX text_idx on test__int using gist ( a gist__intbig_ops );
470519
SELECT count(*) from test__int WHERE a && '{23,50}';
@@ -497,6 +546,18 @@ SELECT count(*) from test__int WHERE a @> '{20,23}';
497546
12
498547
(1 row)
499548

549+
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
550+
count
551+
-------
552+
10
553+
(1 row)
554+
555+
SELECT count(*) from test__int WHERE a = '{73,23,20}';
556+
count
557+
-------
558+
1
559+
(1 row)
560+
500561
SELECT count(*) from test__int WHERE a @@ '50&68';
501562
count
502563
-------
@@ -515,6 +576,18 @@ SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
515576
21
516577
(1 row)
517578

579+
SELECT count(*) from test__int WHERE a @@ '20 | !21';
580+
count
581+
-------
582+
6566
583+
(1 row)
584+
585+
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
586+
count
587+
-------
588+
6343
589+
(1 row)
590+
518591
DROP INDEX text_idx;
519592
CREATE INDEX text_idx on test__int using gin ( a gin__int_ops );
520593
SELECT count(*) from test__int WHERE a && '{23,50}';
@@ -547,6 +620,18 @@ SELECT count(*) from test__int WHERE a @> '{20,23}';
547620
12
548621
(1 row)
549622

623+
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
624+
count
625+
-------
626+
10
627+
(1 row)
628+
629+
SELECT count(*) from test__int WHERE a = '{73,23,20}';
630+
count
631+
-------
632+
1
633+
(1 row)
634+
550635
SELECT count(*) from test__int WHERE a @@ '50&68';
551636
count
552637
-------
@@ -565,3 +650,16 @@ SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
565650
21
566651
(1 row)
567652

653+
SELECT count(*) from test__int WHERE a @@ '20 | !21';
654+
count
655+
-------
656+
6566
657+
(1 row)
658+
659+
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
660+
count
661+
-------
662+
6343
663+
(1 row)
664+
665+
RESET enable_seqscan;

‎contrib/intarray/sql/_int.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -76,9 +76,15 @@ SELECT count(*) from test__int WHERE a @@ '23|50';
7676
SELECTcount(*)from test__intWHERE a @>'{23,50}';
7777
SELECTcount(*)from test__intWHERE a @@'23&50';
7878
SELECTcount(*)from test__intWHERE a @>'{20,23}';
79+
SELECTcount(*)from test__intWHERE a<@'{73,23,20}';
80+
SELECTcount(*)from test__intWHERE a='{73,23,20}';
7981
SELECTcount(*)from test__intWHERE a @@'50&68';
8082
SELECTcount(*)from test__intWHERE a @>'{20,23}'or a @>'{50,68}';
8183
SELECTcount(*)from test__intWHERE a @@'(20&23)|(50&68)';
84+
SELECTcount(*)from test__intWHERE a @@'20 | !21';
85+
SELECTcount(*)from test__intWHERE a @@'!20 & !21';
86+
87+
SET enable_seqscan= off;-- not all of these would use index by default
8288

8389
CREATEINDEXtext_idxon test__int using gist ( a gist__int_ops );
8490

@@ -87,9 +93,13 @@ SELECT count(*) from test__int WHERE a @@ '23|50';
8793
SELECTcount(*)from test__intWHERE a @>'{23,50}';
8894
SELECTcount(*)from test__intWHERE a @@'23&50';
8995
SELECTcount(*)from test__intWHERE a @>'{20,23}';
96+
SELECTcount(*)from test__intWHERE a<@'{73,23,20}';
97+
SELECTcount(*)from test__intWHERE a='{73,23,20}';
9098
SELECTcount(*)from test__intWHERE a @@'50&68';
9199
SELECTcount(*)from test__intWHERE a @>'{20,23}'or a @>'{50,68}';
92100
SELECTcount(*)from test__intWHERE a @@'(20&23)|(50&68)';
101+
SELECTcount(*)from test__intWHERE a @@'20 | !21';
102+
SELECTcount(*)from test__intWHERE a @@'!20 & !21';
93103

94104
DROPINDEX text_idx;
95105
CREATEINDEXtext_idxon test__int using gist ( a gist__intbig_ops );
@@ -99,9 +109,13 @@ SELECT count(*) from test__int WHERE a @@ '23|50';
99109
SELECTcount(*)from test__intWHERE a @>'{23,50}';
100110
SELECTcount(*)from test__intWHERE a @@'23&50';
101111
SELECTcount(*)from test__intWHERE a @>'{20,23}';
112+
SELECTcount(*)from test__intWHERE a<@'{73,23,20}';
113+
SELECTcount(*)from test__intWHERE a='{73,23,20}';
102114
SELECTcount(*)from test__intWHERE a @@'50&68';
103115
SELECTcount(*)from test__intWHERE a @>'{20,23}'or a @>'{50,68}';
104116
SELECTcount(*)from test__intWHERE a @@'(20&23)|(50&68)';
117+
SELECTcount(*)from test__intWHERE a @@'20 | !21';
118+
SELECTcount(*)from test__intWHERE a @@'!20 & !21';
105119

106120
DROPINDEX text_idx;
107121
CREATEINDEXtext_idxon test__int using gin ( a gin__int_ops );
@@ -111,6 +125,12 @@ SELECT count(*) from test__int WHERE a @@ '23|50';
111125
SELECTcount(*)from test__intWHERE a @>'{23,50}';
112126
SELECTcount(*)from test__intWHERE a @@'23&50';
113127
SELECTcount(*)from test__intWHERE a @>'{20,23}';
128+
SELECTcount(*)from test__intWHERE a<@'{73,23,20}';
129+
SELECTcount(*)from test__intWHERE a='{73,23,20}';
114130
SELECTcount(*)from test__intWHERE a @@'50&68';
115131
SELECTcount(*)from test__intWHERE a @>'{20,23}'or a @>'{50,68}';
116132
SELECTcount(*)from test__intWHERE a @@'(20&23)|(50&68)';
133+
SELECTcount(*)from test__intWHERE a @@'20 | !21';
134+
SELECTcount(*)from test__intWHERE a @@'!20 & !21';
135+
136+
RESET enable_seqscan;

0 commit comments

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