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

Commit5b3e6c1

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 parent2fbecb6 commit5b3e6c1

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
SELECTcount(*)from test__intWHERE a @>'{23,50}';
8282
SELECTcount(*)from test__intWHERE a @@'23&50';
8383
SELECTcount(*)from test__intWHERE a @>'{20,23}';
84+
SELECTcount(*)from test__intWHERE a<@'{73,23,20}';
85+
SELECTcount(*)from test__intWHERE a='{73,23,20}';
8486
SELECTcount(*)from test__intWHERE a @@'50&68';
8587
SELECTcount(*)from test__intWHERE a @>'{20,23}'or a @>'{50,68}';
8688
SELECTcount(*)from test__intWHERE a @@'(20&23)|(50&68)';
89+
SELECTcount(*)from test__intWHERE a @@'20 | !21';
90+
SELECTcount(*)from test__intWHERE a @@'!20 & !21';
91+
92+
SET enable_seqscan= off;-- not all of these would use index by default
8793

8894
CREATEINDEXtext_idxon test__int using gist ( a gist__int_ops );
8995

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

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

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

0 commit comments

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