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

Commit3a7a3ea

Browse files
committed
Put "excludeOnly" GIN scan keys at the end of the scankey array.
Commit4b754d6 introduced the concept of an excludeOnly scan key,which cannot select matching index entries but can rejectnon-matching tuples, for example a tsquery such as '!term'. There arepoorly-documented assumptions that such scan keys do not appear as thefirst scan key. ginNewScanKey did nothing to ensure that, however,with the result that certain GIN index searches could go into aninfinite loop while apparently-equivalent queries with the clauses ina different order were fine.Fix by teaching ginNewScanKey to place all excludeOnly scan keysafter all not-excludeOnly ones. So far as we know at present,it might be sufficient to avoid the case where the very firstscan key is excludeOnly; but I'm not very convinced that therearen't other dependencies on the ordering.Bug: #19031Reported-by: Tim Wood <washwithcare@gmail.com>Author: Tom Lane <tgl@sss.pgh.pa.us>Discussion:https://postgr.es/m/19031-0638148643d25548@postgresql.orgBackpatch-through: 13
1 parent44c2e5b commit3a7a3ea

File tree

3 files changed

+97
-0
lines changed

3 files changed

+97
-0
lines changed

‎contrib/pg_trgm/expected/pg_trgm.out‎

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4693,6 +4693,23 @@ select count(*) from test_trgm where t like '%99%' and t like '%qw%';
46934693
19
46944694
(1 row)
46954695

4696+
explain (costs off)
4697+
select count(*) from test_trgm where t %> '' and t %> '%qwerty%';
4698+
QUERY PLAN
4699+
-------------------------------------------------------------------------
4700+
Aggregate
4701+
-> Bitmap Heap Scan on test_trgm
4702+
Recheck Cond: ((t %> ''::text) AND (t %> '%qwerty%'::text))
4703+
-> Bitmap Index Scan on trgm_idx
4704+
Index Cond: ((t %> ''::text) AND (t %> '%qwerty%'::text))
4705+
(5 rows)
4706+
4707+
select count(*) from test_trgm where t %> '' and t %> '%qwerty%';
4708+
count
4709+
-------
4710+
0
4711+
(1 row)
4712+
46964713
-- ensure that pending-list items are handled correctly, too
46974714
create temp table t_test_trgm(t text COLLATE "C");
46984715
create index t_trgm_idx on t_test_trgm using gin (t gin_trgm_ops);
@@ -4731,6 +4748,23 @@ select count(*) from t_test_trgm where t like '%99%' and t like '%qw%';
47314748
1
47324749
(1 row)
47334750

4751+
explain (costs off)
4752+
select count(*) from t_test_trgm where t %> '' and t %> '%qwerty%';
4753+
QUERY PLAN
4754+
-------------------------------------------------------------------------
4755+
Aggregate
4756+
-> Bitmap Heap Scan on t_test_trgm
4757+
Recheck Cond: ((t %> ''::text) AND (t %> '%qwerty%'::text))
4758+
-> Bitmap Index Scan on t_trgm_idx
4759+
Index Cond: ((t %> ''::text) AND (t %> '%qwerty%'::text))
4760+
(5 rows)
4761+
4762+
select count(*) from t_test_trgm where t %> '' and t %> '%qwerty%';
4763+
count
4764+
-------
4765+
0
4766+
(1 row)
4767+
47344768
-- run the same queries with sequential scan to check the results
47354769
set enable_bitmapscan=off;
47364770
set enable_seqscan=on;
@@ -4746,6 +4780,12 @@ select count(*) from test_trgm where t like '%99%' and t like '%qw%';
47464780
19
47474781
(1 row)
47484782

4783+
select count(*) from test_trgm where t %> '' and t %> '%qwerty%';
4784+
count
4785+
-------
4786+
0
4787+
(1 row)
4788+
47494789
select count(*) from t_test_trgm where t like '%99%' and t like '%qwerty%';
47504790
count
47514791
-------
@@ -4758,6 +4798,12 @@ select count(*) from t_test_trgm where t like '%99%' and t like '%qw%';
47584798
1
47594799
(1 row)
47604800

4801+
select count(*) from t_test_trgm where t %> '' and t %> '%qwerty%';
4802+
count
4803+
-------
4804+
0
4805+
(1 row)
4806+
47614807
reset enable_bitmapscan;
47624808
create table test2(t text COLLATE "C");
47634809
insert into test2 values ('abcdef');

‎contrib/pg_trgm/sql/pg_trgm.sql‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -80,6 +80,9 @@ select count(*) from test_trgm where t like '%99%' and t like '%qwerty%';
8080
explain (costs off)
8181
selectcount(*)from test_trgmwhere tlike'%99%'and tlike'%qw%';
8282
selectcount(*)from test_trgmwhere tlike'%99%'and tlike'%qw%';
83+
explain (costs off)
84+
selectcount(*)from test_trgmwhere t %>''and t %>'%qwerty%';
85+
selectcount(*)from test_trgmwhere t %>''and t %>'%qwerty%';
8386
-- ensure that pending-list items are handled correctly, too
8487
create temp table t_test_trgm(ttext COLLATE"C");
8588
createindext_trgm_idxon t_test_trgm using gin (t gin_trgm_ops);
@@ -90,14 +93,19 @@ select count(*) from t_test_trgm where t like '%99%' and t like '%qwerty%';
9093
explain (costs off)
9194
selectcount(*)from t_test_trgmwhere tlike'%99%'and tlike'%qw%';
9295
selectcount(*)from t_test_trgmwhere tlike'%99%'and tlike'%qw%';
96+
explain (costs off)
97+
selectcount(*)from t_test_trgmwhere t %>''and t %>'%qwerty%';
98+
selectcount(*)from t_test_trgmwhere t %>''and t %>'%qwerty%';
9399

94100
-- run the same queries with sequential scan to check the results
95101
set enable_bitmapscan=off;
96102
set enable_seqscan=on;
97103
selectcount(*)from test_trgmwhere tlike'%99%'and tlike'%qwerty%';
98104
selectcount(*)from test_trgmwhere tlike'%99%'and tlike'%qw%';
105+
selectcount(*)from test_trgmwhere t %>''and t %>'%qwerty%';
99106
selectcount(*)from t_test_trgmwhere tlike'%99%'and tlike'%qwerty%';
100107
selectcount(*)from t_test_trgmwhere tlike'%99%'and tlike'%qw%';
108+
selectcount(*)from t_test_trgmwhere t %>''and t %>'%qwerty%';
101109
reset enable_bitmapscan;
102110

103111
createtabletest2(ttext COLLATE"C");

‎src/backend/access/gin/ginscan.c‎

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -271,6 +271,7 @@ ginNewScanKey(IndexScanDesc scan)
271271
ScanKeyscankey=scan->keyData;
272272
GinScanOpaqueso= (GinScanOpaque)scan->opaque;
273273
inti;
274+
intnumExcludeOnly;
274275
boolhasNullQuery= false;
275276
boolattrHasNormalScan[INDEX_MAX_KEYS]= {false};
276277
MemoryContextoldCtx;
@@ -393,6 +394,7 @@ ginNewScanKey(IndexScanDesc scan)
393394
* excludeOnly scan key must receive a GIN_CAT_EMPTY_QUERY hidden entry
394395
* and be set to normal (excludeOnly = false).
395396
*/
397+
numExcludeOnly=0;
396398
for (i=0;i<so->nkeys;i++)
397399
{
398400
GinScanKeykey=&so->keys[i];
@@ -406,6 +408,47 @@ ginNewScanKey(IndexScanDesc scan)
406408
ginScanKeyAddHiddenEntry(so,key,GIN_CAT_EMPTY_QUERY);
407409
attrHasNormalScan[key->attnum-1]= true;
408410
}
411+
else
412+
numExcludeOnly++;
413+
}
414+
415+
/*
416+
* If we left any excludeOnly scan keys as-is, move them to the end of the
417+
* scan key array: they must appear after normal key(s).
418+
*/
419+
if (numExcludeOnly>0)
420+
{
421+
GinScanKeytmpkeys;
422+
intiNormalKey;
423+
intiExcludeOnly;
424+
425+
/* We'd better have made at least one normal key */
426+
Assert(numExcludeOnly<so->nkeys);
427+
/* Make a temporary array to hold the re-ordered scan keys */
428+
tmpkeys= (GinScanKey)palloc(so->nkeys*sizeof(GinScanKeyData));
429+
/* Re-order the keys ... */
430+
iNormalKey=0;
431+
iExcludeOnly=so->nkeys-numExcludeOnly;
432+
for (i=0;i<so->nkeys;i++)
433+
{
434+
GinScanKeykey=&so->keys[i];
435+
436+
if (key->excludeOnly)
437+
{
438+
memcpy(tmpkeys+iExcludeOnly,key,sizeof(GinScanKeyData));
439+
iExcludeOnly++;
440+
}
441+
else
442+
{
443+
memcpy(tmpkeys+iNormalKey,key,sizeof(GinScanKeyData));
444+
iNormalKey++;
445+
}
446+
}
447+
Assert(iNormalKey==so->nkeys-numExcludeOnly);
448+
Assert(iExcludeOnly==so->nkeys);
449+
/* ... and copy them back to so->keys[] */
450+
memcpy(so->keys,tmpkeys,so->nkeys*sizeof(GinScanKeyData));
451+
pfree(tmpkeys);
409452
}
410453

411454
/*

0 commit comments

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