Skip to content

Commit 5b3e6c1

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 2fbecb6 commit 5b3e6c1

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
@@ -96,8 +96,13 @@ g_int_consistent(PG_FUNCTION_ARGS)
9696
retval = inner_int_contains(query,
9797
(ArrayType *) DatumGetPointer(entry->key));
9898
else
99-
retval = inner_int_overlap((ArrayType *) DatumGetPointer(entry->key),
100-
query);
99+
{
100+
/*
101+
* Unfortunately, because empty arrays could be anywhere in
102+
* the index, we must search the whole tree.
103+
*/
104+
retval = true;
105+
}
101106
break;
102107
default:
103108
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
@@ -407,6 +407,18 @@ SELECT count(*) from test__int WHERE a @> '{20,23}';
407407
12
408408
(1 row)
409409

410+
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
411+
count
412+
-------
413+
10
414+
(1 row)
415+
416+
SELECT count(*) from test__int WHERE a = '{73,23,20}';
417+
count
418+
-------
419+
1
420+
(1 row)
421+
410422
SELECT count(*) from test__int WHERE a @@ '50&68';
411423
count
412424
-------
@@ -425,6 +437,19 @@ SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
425437
21
426438
(1 row)
427439

440+
SELECT count(*) from test__int WHERE a @@ '20 | !21';
441+
count
442+
-------
443+
6566
444+
(1 row)
445+
446+
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
447+
count
448+
-------
449+
6343
450+
(1 row)
451+
452+
SET enable_seqscan = off; -- not all of these would use index by default
428453
CREATE INDEX text_idx on test__int using gist ( a gist__int_ops );
429454
SELECT count(*) from test__int WHERE a && '{23,50}';
430455
count
@@ -456,6 +481,18 @@ SELECT count(*) from test__int WHERE a @> '{20,23}';
456481
12
457482
(1 row)
458483

484+
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
485+
count
486+
-------
487+
10
488+
(1 row)
489+
490+
SELECT count(*) from test__int WHERE a = '{73,23,20}';
491+
count
492+
-------
493+
1
494+
(1 row)
495+
459496
SELECT count(*) from test__int WHERE a @@ '50&68';
460497
count
461498
-------
@@ -474,6 +511,18 @@ SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
474511
21
475512
(1 row)
476513

514+
SELECT count(*) from test__int WHERE a @@ '20 | !21';
515+
count
516+
-------
517+
6566
518+
(1 row)
519+
520+
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
521+
count
522+
-------
523+
6343
524+
(1 row)
525+
477526
DROP INDEX text_idx;
478527
CREATE INDEX text_idx on test__int using gist ( a gist__intbig_ops );
479528
SELECT count(*) from test__int WHERE a && '{23,50}';
@@ -506,6 +555,18 @@ SELECT count(*) from test__int WHERE a @> '{20,23}';
506555
12
507556
(1 row)
508557

558+
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
559+
count
560+
-------
561+
10
562+
(1 row)
563+
564+
SELECT count(*) from test__int WHERE a = '{73,23,20}';
565+
count
566+
-------
567+
1
568+
(1 row)
569+
509570
SELECT count(*) from test__int WHERE a @@ '50&68';
510571
count
511572
-------
@@ -524,6 +585,18 @@ SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
524585
21
525586
(1 row)
526587

588+
SELECT count(*) from test__int WHERE a @@ '20 | !21';
589+
count
590+
-------
591+
6566
592+
(1 row)
593+
594+
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
595+
count
596+
-------
597+
6343
598+
(1 row)
599+
527600
DROP INDEX text_idx;
528601
CREATE INDEX text_idx on test__int using gin ( a gin__int_ops );
529602
SELECT count(*) from test__int WHERE a && '{23,50}';
@@ -556,6 +629,18 @@ SELECT count(*) from test__int WHERE a @> '{20,23}';
556629
12
557630
(1 row)
558631

632+
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
633+
count
634+
-------
635+
10
636+
(1 row)
637+
638+
SELECT count(*) from test__int WHERE a = '{73,23,20}';
639+
count
640+
-------
641+
1
642+
(1 row)
643+
559644
SELECT count(*) from test__int WHERE a @@ '50&68';
560645
count
561646
-------
@@ -574,3 +659,16 @@ SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
574659
21
575660
(1 row)
576661

662+
SELECT count(*) from test__int WHERE a @@ '20 | !21';
663+
count
664+
-------
665+
6566
666+
(1 row)
667+
668+
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
669+
count
670+
-------
671+
6343
672+
(1 row)
673+
674+
RESET enable_seqscan;

‎contrib/intarray/sql/_int.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -81,9 +81,15 @@ SELECT count(*) from test__int WHERE a @@ '23|50';
8181
SELECT count(*) from test__int WHERE a @> '{23,50}';
8282
SELECT count(*) from test__int WHERE a @@ '23&50';
8383
SELECT count(*) from test__int WHERE a @> '{20,23}';
84+
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
85+
SELECT count(*) from test__int WHERE a = '{73,23,20}';
8486
SELECT count(*) from test__int WHERE a @@ '50&68';
8587
SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}';
8688
SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
89+
SELECT count(*) from test__int WHERE a @@ '20 | !21';
90+
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
91+
92+
SET enable_seqscan = off; -- not all of these would use index by default
8793

8894
CREATE INDEX text_idx on test__int using gist ( a gist__int_ops );
8995

@@ -92,9 +98,13 @@ SELECT count(*) from test__int WHERE a @@ '23|50';
9298
SELECT count(*) from test__int WHERE a @> '{23,50}';
9399
SELECT count(*) from test__int WHERE a @@ '23&50';
94100
SELECT count(*) from test__int WHERE a @> '{20,23}';
101+
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
102+
SELECT count(*) from test__int WHERE a = '{73,23,20}';
95103
SELECT count(*) from test__int WHERE a @@ '50&68';
96104
SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}';
97105
SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
106+
SELECT count(*) from test__int WHERE a @@ '20 | !21';
107+
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
98108

99109
DROP INDEX text_idx;
100110
CREATE INDEX text_idx on test__int using gist ( a gist__intbig_ops );
@@ -104,9 +114,13 @@ SELECT count(*) from test__int WHERE a @@ '23|50';
104114
SELECT count(*) from test__int WHERE a @> '{23,50}';
105115
SELECT count(*) from test__int WHERE a @@ '23&50';
106116
SELECT count(*) from test__int WHERE a @> '{20,23}';
117+
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
118+
SELECT count(*) from test__int WHERE a = '{73,23,20}';
107119
SELECT count(*) from test__int WHERE a @@ '50&68';
108120
SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}';
109121
SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
122+
SELECT count(*) from test__int WHERE a @@ '20 | !21';
123+
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
110124

111125
DROP INDEX text_idx;
112126
CREATE INDEX text_idx on test__int using gin ( a gin__int_ops );
@@ -116,6 +130,12 @@ SELECT count(*) from test__int WHERE a @@ '23|50';
116130
SELECT count(*) from test__int WHERE a @> '{23,50}';
117131
SELECT count(*) from test__int WHERE a @@ '23&50';
118132
SELECT count(*) from test__int WHERE a @> '{20,23}';
133+
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
134+
SELECT count(*) from test__int WHERE a = '{73,23,20}';
119135
SELECT count(*) from test__int WHERE a @@ '50&68';
120136
SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}';
121137
SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
138+
SELECT count(*) from test__int WHERE a @@ '20 | !21';
139+
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
140+
141+
RESET enable_seqscan;

0 commit comments

Comments
 (0)