Skip to content

Commit 614119d

Browse files
tglsfdctglsfdc
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 empty array values (which of course should trivially match such queries). This is because the test condition to see whether we should descend through 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 way to fix this except by lobotomizing <@ indexscans to scan the whole index ... 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 impacted have a couple of options. They could switch to a GIN index, which doesn'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 find all non-empty subsets of the given constant-array, which is all that could reliably be expected of the query before. While at it, add some more regression test cases to improve code coverage of contrib/intarray. In passing, adjust resize_intArrayType so that when it's returning an empty array, it uses construct_empty_array for that rather than cowboy hacking on the input array. While the hack produces an array that looks valid for most purposes, it isn't bitwise equal to empty arrays produced by other code paths, which could have subtle odd effects. I don't think this code path is performance-critical enough to justify such shortcuts. (Back-patch this part only as far as v11; before commit 01783ac we were not careful about this in other intarray code paths either.) Back-patch the <@ fixes to all supported versions, since this was broken from day one. Patch by me; thanks to Alexander Korotkov for review. Discussion: https://postgr.es/m/458.1565114141@sss.pgh.pa.us
1 parent 246893d commit 614119d

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
SELECT count(*) from test__int WHERE a @> '{23,50}';
7777
SELECT count(*) from test__int WHERE a @@ '23&50';
7878
SELECT count(*) from test__int WHERE a @> '{20,23}';
79+
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
80+
SELECT count(*) from test__int WHERE a = '{73,23,20}';
7981
SELECT count(*) from test__int WHERE a @@ '50&68';
8082
SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}';
8183
SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
84+
SELECT count(*) from test__int WHERE a @@ '20 | !21';
85+
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
86+
87+
SET enable_seqscan = off; -- not all of these would use index by default
8288

8389
CREATE INDEX text_idx on test__int using gist ( a gist__int_ops );
8490

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

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

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

0 commit comments

Comments
 (0)