Hi Heikki, > With such a large number of key items, it would be much > faster to sort the key items, and do a "merge join" of the key items and > the item on the page.
Here is a patch set along those lines, v2 attached. The locking is unchanged. 0001 sorts the query entries once. For each pending list row, it then looks up the row's keys in them with a binary search. That is faster than a plain merge, because a row usually has far fewer keys than the query. In Kirill's case, a 1s statement_timeout fires after 202s on master and after 1s with 0001. With a default size pending list, 1000 entry queries are 22x faster. Queries with fewer than 64 entries take the old path. 0002 calls the consistent functions after the pending list lock is released. It helps when many rows match. 0001 can go in without it. 0003 adds tests. Large arrays are still slow in startScanKey(), but it holds no lock. Thanks, Shihao
From 0123088039df76c610042400f075c06f69e7afa0 Mon Sep 17 00:00:00 2001 From: Shihao <[email protected]> Date: Wed, 23 Sep 2026 11:09:48 -0400 Subject: [PATCH v2 2/3] Call GIN consistent functions for pending list rows after the scan With many scan entries, the consistent functions can be slow too. For such scan keys, remember the matching rows and call the consistent functions after the pending list lock is released, unless that would use more than work_mem. Discussion: https://postgr.es/m/CALdSSPisYC-zHiWJU36AP-zrPN-im-gEZrK-QD8TPn3Vb8xMvw@mail.gmail.com --- src/backend/access/gin/ginget.c | 204 +++++++++++++++++++++++++++---- src/tools/pgindent/typedefs.list | 1 + 2 files changed, 182 insertions(+), 23 deletions(-) diff --git a/src/backend/access/gin/ginget.c b/src/backend/access/gin/ginget.c index 7d820098ea9..90772a6bd3f 100644 --- a/src/backend/access/gin/ginget.c +++ b/src/backend/access/gin/ginget.c @@ -63,6 +63,22 @@ typedef struct pendingPosition bool entryResDirty; } pendingPosition; +/* + * Matching pending-list rows whose consistent functions have not been called + * yet, see scanPendingInsert(). + */ +typedef struct pendingMatches +{ + /* heap TIDs of the rows */ + ItemPointerData *items; + uint32 nitems; + uint32 maxitems; + /* for each row and scan key: number of true entries, then their indexes */ + uint32 *entries; + Size nentries; + Size maxentries; +} pendingMatches; + /* * Goes to the next page if current offset is outside of bounds @@ -2052,6 +2068,151 @@ collectMatchesForHeapRow(IndexScanDesc scan, pendingPosition *pos) return true; } +/* + * Call the consistent functions of all scan keys, with the current entryRes + * arrays. Returns true if the row matches, and sets *recheck. + */ +static bool +pendingRowIsConsistent(GinScanOpaque so, bool *recheck) +{ + MemoryContext oldCtx; + bool match = true; + + oldCtx = MemoryContextSwitchTo(so->tempCtx); + *recheck = false; + + for (uint32 i = 0; i < so->nkeys; i++) + { + GinScanKey key = so->keys + i; + + if (!key->boolConsistentFn(key)) + { + match = false; + break; + } + *recheck |= key->recheckCurItem; + } + + MemoryContextSwitchTo(oldCtx); + MemoryContextReset(so->tempCtx); + + return match; +} + +/* + * Remember the current heap row and its true entries, so that its consistent + * functions can be called after the pending list has been scanned. Returns + * false, and remembers nothing, if that would use more than work_mem. That + * is in addition to the bitmap, which has its own work_mem limit. + */ +static bool +savePendingMatch(GinScanOpaque so, pendingPosition *pos, pendingMatches *m) +{ + Size needed = so->nkeys; + + for (uint32 i = 0; i < so->nkeys; i++) + { + pendingKeyState *ks = &pos->keyState[i]; + + needed += ks->trueEntries ? ks->ntrueEntries : so->keys[i].nentries; + } + + if (m->nitems == m->maxitems || m->nentries + needed > m->maxentries) + { + uint32 maxitems = m->maxitems; + Size maxentries = m->maxentries; + + if (m->nitems == maxitems) + maxitems = Max(maxitems * 2, 64); + while (m->nentries + needed > maxentries) + maxentries = Max(maxentries * 2, 1024); + + if (maxitems * sizeof(ItemPointerData) + maxentries * sizeof(uint32) > + Min((Size) work_mem * 1024, MaxAllocSize)) + return false; + + if (m->items == NULL) + { + m->items = palloc_array(ItemPointerData, maxitems); + m->entries = palloc_array(uint32, maxentries); + } + else + { + m->items = repalloc_array(m->items, ItemPointerData, maxitems); + m->entries = repalloc_array(m->entries, uint32, maxentries); + } + m->maxitems = maxitems; + m->maxentries = maxentries; + } + + m->items[m->nitems++] = pos->item; + for (uint32 i = 0; i < so->nkeys; i++) + { + GinScanKey key = so->keys + i; + pendingKeyState *ks = &pos->keyState[i]; + + if (ks->trueEntries) + { + m->entries[m->nentries++] = ks->ntrueEntries; + memcpy(&m->entries[m->nentries], ks->trueEntries, + ks->ntrueEntries * sizeof(uint32)); + m->nentries += ks->ntrueEntries; + } + else + { + Size countpos = m->nentries++; + uint32 count = 0; + + for (uint32 j = 0; j < key->nentries; j++) + { + if (key->entryRes[j]) + { + m->entries[m->nentries++] = j; + count++; + } + } + m->entries[countpos] = count; + } + } + + return true; +} + +/* + * Call the consistent functions for the rows remembered by savePendingMatch(), + * and add the matching ones to the bitmap. No buffer locks are held here, so + * this can be interrupted. + */ +static void +checkPendingMatches(GinScanOpaque so, pendingMatches *m, + TIDBitmap *tbm, int64 *ntids) +{ + Size off = 0; + + for (uint32 r = 0; r < m->nitems; r++) + { + bool recheck; + + CHECK_FOR_INTERRUPTS(); + + for (uint32 i = 0; i < so->nkeys; i++) + { + GinScanKey key = so->keys + i; + uint32 count = m->entries[off++]; + + memset(key->entryRes, GIN_FALSE, key->nentries); + for (uint32 k = 0; k < count; k++) + key->entryRes[m->entries[off++]] = GIN_TRUE; + } + + if (pendingRowIsConsistent(so, &recheck)) + { + tbm_add_tuples(tbm, &m->items[r], 1, recheck); + (*ntids)++; + } + } +} + /* * Collect all matched rows from pending list into bitmap. */ @@ -2059,10 +2220,9 @@ static void scanPendingInsert(IndexScanDesc scan, TIDBitmap *tbm, int64 *ntids) { GinScanOpaque so = (GinScanOpaque) scan->opaque; - MemoryContext oldCtx; - bool recheck, - match; + bool recheck; pendingPosition pos; + pendingMatches matches = {0}; Buffer metabuffer = ReadBuffer(scan->indexRelation, GIN_METAPAGE_BLKNO); Page page; BlockNumber blkno; @@ -2134,31 +2294,21 @@ scanPendingInsert(IndexScanDesc scan, TIDBitmap *tbm, int64 *ntids) if (!collectMatchesForHeapRow(scan, &pos)) continue; + /* + * With scan keys that have many entries, a consistent function call + * can take a while, and we are holding a buffer lock that keeps us + * from being canceled. So just remember the row, and call the + * consistent functions after the scan, if it fits in work_mem. + */ + if (pos.keyState != NULL && savePendingMatch(so, &pos, &matches)) + continue; + /* * Matching of entries of one row is finished, so check row using * consistent functions. */ - oldCtx = MemoryContextSwitchTo(so->tempCtx); - recheck = false; - match = true; pos.entryResDirty = true; - - for (uint32 i = 0; i < so->nkeys; i++) - { - GinScanKey key = so->keys + i; - - if (!key->boolConsistentFn(key)) - { - match = false; - break; - } - recheck |= key->recheckCurItem; - } - - MemoryContextSwitchTo(oldCtx); - MemoryContextReset(so->tempCtx); - - if (match) + if (pendingRowIsConsistent(so, &recheck)) { tbm_add_tuples(tbm, &pos.item, 1, recheck); (*ntids)++; @@ -2166,6 +2316,14 @@ scanPendingInsert(IndexScanDesc scan, TIDBitmap *tbm, int64 *ntids) } pfree(pos.hasMatchKey); + + /* scanGetCandidate() has released the last pending list page */ + if (matches.items != NULL) + { + checkPendingMatches(so, &matches, tbm, ntids); + pfree(matches.items); + pfree(matches.entries); + } } diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index f17ca41f8cc..91d77b6bee1 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -4004,6 +4004,7 @@ pe_test_config pe_test_escape_func pe_test_vector pendingKeyState +pendingMatches pendingPosition pending_label pgParameterStatus -- 2.37.1 (Apple Git-137.1)
From 0bb0e6056f13ff820f9333b3722fd40ca11b2634 Mon Sep 17 00:00:00 2001 From: Shihao <[email protected]> Date: Wed, 23 Sep 2026 09:26:49 -0400 Subject: [PATCH v2 1/3] Speed up GIN pending list scans for scan keys with many entries Matching a pending list row looked up every scan key entry in the row's tuples while holding a pending list page lock. With a large query array this could take minutes and could not be canceled. For scan keys with 64 or more entries, sort the entries first and look up the row's tuples in them instead. Reported-by: Kirill Reshke <[email protected]> Suggested-by: Heikki Linnakangas <[email protected]> Discussion: https://postgr.es/m/CALdSSPisYC-zHiWJU36AP-zrPN-im-gEZrK-QD8TPn3Vb8xMvw@mail.gmail.com --- src/backend/access/gin/ginget.c | 253 ++++++++++++++++++++++++++++++- src/tools/pgindent/typedefs.list | 2 + 2 files changed, 252 insertions(+), 3 deletions(-) diff --git a/src/backend/access/gin/ginget.c b/src/backend/access/gin/ginget.c index 2bcb32ca3d0..7d820098ea9 100644 --- a/src/backend/access/gin/ginget.c +++ b/src/backend/access/gin/ginget.c @@ -26,6 +26,30 @@ /* GUC parameter */ int GinFuzzySearchLimit = 0; +/* + * Scan keys with at least this many entries get their entries sorted before + * the pending list is scanned, see collectMatchesForHeapRow(). Below that, + * looking up each entry separately is cheap enough. + */ +#define GIN_PENDING_SORT_THRESHOLD 64 + +/* + * Per-scan-key state for matching pending-list tuples, used only for scan + * keys with at least GIN_PENDING_SORT_THRESHOLD entries. + */ +typedef struct pendingKeyState +{ + /* indexes of exact-match entries, sorted by key */ + uint32 *sortedEntries; + uint32 nsortedEntries; + /* indexes of partial-match and GIN_CAT_EMPTY_QUERY entries */ + uint32 *otherEntries; + uint32 notherEntries; + /* indexes of entries set to GIN_TRUE for the current heap row */ + uint32 *trueEntries; + uint32 ntrueEntries; +} pendingKeyState; + typedef struct pendingPosition { Buffer pendingBuffer; @@ -33,6 +57,10 @@ typedef struct pendingPosition OffsetNumber lastOffset; ItemPointerData item; bool *hasMatchKey; + /* array of nkeys elements, or NULL if no scan key has many entries */ + pendingKeyState *keyState; + /* consistent functions have been called since entryRes was reset */ + bool entryResDirty; } pendingPosition; @@ -1606,6 +1634,161 @@ matchPartialInPendingList(GinState *ginstate, Page page, return false; } +typedef struct entrySortArg +{ + GinState *ginstate; + GinScanKey key; +} entrySortArg; + +/* + * Comparison function for scan entry indexes. Sorts by the entry's key, in + * the same order as the pending-list tuples of a heap row. + */ +static int +entryIndexByKeyCmp(const void *a1, const void *a2, void *arg) +{ + entrySortArg *sortarg = arg; + GinScanEntry e1 = sortarg->key->scanEntry[*(const uint32 *) a1]; + GinScanEntry e2 = sortarg->key->scanEntry[*(const uint32 *) a2]; + + return ginCompareEntries(sortarg->ginstate, sortarg->key->attnum, + e1->queryKey, e1->queryCategory, + e2->queryKey, e2->queryCategory); +} + +/* + * Set up the pendingKeyState array for scanning the pending list. + * + * This sorts the entries of the scan keys, so the caller should not hold any + * buffer locks. + */ +static pendingKeyState * +initPendingKeyStates(GinScanOpaque so) +{ + pendingKeyState *keyState; + MemoryContext oldCtx; + + /* This lives as long as the scan keys */ + oldCtx = MemoryContextSwitchTo(so->keyCtx); + + keyState = palloc0_array(pendingKeyState, so->nkeys); + for (uint32 i = 0; i < so->nkeys; i++) + { + GinScanKey key = so->keys + i; + pendingKeyState *ks = &keyState[i]; + entrySortArg sortarg; + + if (key->nentries < GIN_PENDING_SORT_THRESHOLD) + continue; + + ks->sortedEntries = palloc_array(uint32, key->nentries); + ks->otherEntries = palloc_array(uint32, key->nentries); + ks->trueEntries = palloc_array(uint32, key->nentries); + + for (uint32 j = 0; j < key->nentries; j++) + { + GinScanEntry entry = key->scanEntry[j]; + + if (entry->isPartialMatch || + entry->queryCategory == GIN_CAT_EMPTY_QUERY) + ks->otherEntries[ks->notherEntries++] = j; + else + ks->sortedEntries[ks->nsortedEntries++] = j; + } + + sortarg.ginstate = &so->ginstate; + sortarg.key = key; + qsort_arg(ks->sortedEntries, ks->nsortedEntries, sizeof(uint32), + entryIndexByKeyCmp, &sortarg); + + CHECK_FOR_INTERRUPTS(); + } + + MemoryContextSwitchTo(oldCtx); + + return keyState; +} + +/* + * Set entryRes for the sorted entries of a scan key that are equal to one of + * the pending-list tuples of the current heap row on this page. + * + * The tuples and the sorted entries are in the same order, so we look up each + * tuple in the entries with a binary search, starting where the search for + * the previous tuple ended. With T tuples and E entries this costs about + * T * log(E) comparisons, instead of E * log(T) when looking up each entry + * in the tuples. + */ +static void +matchSortedEntries(GinScanOpaque so, GinScanKey key, pendingKeyState *ks, + Page page, pendingPosition *pos, + Datum *datum, GinNullCategory *category, + bool *datumExtracted) +{ + uint32 lo = 0; + + for (OffsetNumber off = pos->firstOffset; off < pos->lastOffset; off++) + { + IndexTuple itup; + OffsetNumber attrnum; + uint32 hi; + + itup = (IndexTuple) PageGetItem(page, PageGetItemId(page, off)); + attrnum = gintuple_get_attrnum(&so->ginstate, itup); + if (attrnum < key->attnum) + continue; + if (attrnum > key->attnum) + break; + + if (datumExtracted[off - 1] == false) + { + datum[off - 1] = gintuple_get_key(&so->ginstate, itup, + &category[off - 1]); + datumExtracted[off - 1] = true; + } + + /* Find the first entry that is not less than the tuple */ + hi = ks->nsortedEntries; + while (lo < hi) + { + uint32 mid = lo + ((hi - lo) >> 1); + GinScanEntry entry = key->scanEntry[ks->sortedEntries[mid]]; + + if (ginCompareEntries(&so->ginstate, key->attnum, + entry->queryKey, entry->queryCategory, + datum[off - 1], category[off - 1]) < 0) + lo = mid + 1; + else + hi = mid; + } + + /* + * Set all entries equal to the tuple. The query can have duplicates, + * and each of them has its own entryRes slot. + */ + for (; lo < ks->nsortedEntries; lo++) + { + uint32 j = ks->sortedEntries[lo]; + GinScanEntry entry = key->scanEntry[j]; + + if (ginCompareEntries(&so->ginstate, key->attnum, + entry->queryKey, entry->queryCategory, + datum[off - 1], category[off - 1]) != 0) + break; + + if (!key->entryRes[j]) + { + key->entryRes[j] = GIN_TRUE; + ks->trueEntries[ks->ntrueEntries++] = j; + } + } + + /* No later tuple can match any entry */ + if (lo >= ks->nsortedEntries) + break; + } +} + /* * Set up the entryRes array for each key by looking at * every entry for current heap row in pending list. @@ -1616,6 +1799,14 @@ matchPartialInPendingList(GinState *ginstate, Page page, * cannot be returned by the normal search since no entry stream will * source its TID.) * + * For a scan key with many entries, we avoid work proportional to the number + * of entries where we can, because this is done for every heap row in the + * pending list, while holding a buffer lock. If the row has fewer tuples + * than the key has sorted entries, we look up each tuple in the entries + * rather than each entry in the tuples. And instead of clearing the whole + * entryRes array for each row, we clear only the entries set for the + * previous row. + * * The pendingBuffer is presumed pinned and share-locked on entry. */ static bool @@ -1627,15 +1818,27 @@ collectMatchesForHeapRow(IndexScanDesc scan, pendingPosition *pos) IndexTuple itup; /* - * Reset all entryRes and hasMatchKey flags + * Reset all entryRes and hasMatchKey flags. The consistent functions get + * a pointer to entryRes, and we don't rely on them leaving it alone, so + * if they were called for the previous row, clear everything. */ for (uint32 i = 0; i < so->nkeys; i++) { GinScanKey key = so->keys + i; + pendingKeyState *ks = pos->keyState ? &pos->keyState[i] : NULL; - memset(key->entryRes, GIN_FALSE, key->nentries); + if (ks != NULL && ks->trueEntries != NULL && !pos->entryResDirty) + { + for (uint32 k = 0; k < ks->ntrueEntries; k++) + key->entryRes[ks->trueEntries[k]] = GIN_FALSE; + } + else + memset(key->entryRes, GIN_FALSE, key->nentries); + if (ks != NULL) + ks->ntrueEntries = 0; } memset(pos->hasMatchKey, false, so->nkeys); + pos->entryResDirty = false; /* * Outer loop iterates over multiple pending-list pages when a single heap @@ -1656,9 +1859,28 @@ collectMatchesForHeapRow(IndexScanDesc scan, pendingPosition *pos) for (uint32 i = 0; i < so->nkeys; i++) { GinScanKey key = so->keys + i; + pendingKeyState *ks = pos->keyState ? &pos->keyState[i] : NULL; + uint32 *entryList = NULL; + uint32 nentryList = key->nentries; - for (uint32 j = 0; j < key->nentries; j++) + if (ks != NULL && + pos->lastOffset - pos->firstOffset < ks->nsortedEntries) { + /* + * Look up the tuples in the sorted entries, then check the + * remaining entries one by one below. + */ + matchSortedEntries(so, key, ks, page, pos, + datum, category, datumExtracted); + if (ks->ntrueEntries > 0) + pos->hasMatchKey[i] = true; + entryList = ks->otherEntries; + nentryList = ks->notherEntries; + } + + for (uint32 n = 0; n < nentryList; n++) + { + uint32 j = entryList ? entryList[n] : n; GinScanEntry entry = key->scanEntry[j]; OffsetNumber StopLow = pos->firstOffset, StopHigh = pos->lastOffset, @@ -1785,6 +2007,8 @@ collectMatchesForHeapRow(IndexScanDesc scan, pendingPosition *pos) } pos->hasMatchKey[i] |= key->entryRes[j]; + if (key->entryRes[j] && ks != NULL && ks->trueEntries != NULL) + ks->trueEntries[ks->ntrueEntries++] = j; } } @@ -1855,6 +2079,27 @@ scanPendingInsert(IndexScanDesc scan, TIDBitmap *tbm, int64 *ntids) page = BufferGetPage(metabuffer); blkno = GinPageGetMeta(page)->head; + /* + * If there is a pending list, set up the per-key state for matching. That + * can involve sorting a lot of entries, so don't hold the metapage lock + * while doing it, and look at the head of the list again afterwards. + */ + pos.keyState = NULL; + if (blkno != InvalidBlockNumber) + { + for (uint32 i = 0; i < so->nkeys; i++) + { + if (so->keys[i].nentries >= GIN_PENDING_SORT_THRESHOLD) + { + LockBuffer(metabuffer, GIN_UNLOCK); + pos.keyState = initPendingKeyStates(so); + LockBuffer(metabuffer, GIN_SHARE); + blkno = GinPageGetMeta(page)->head; + break; + } + } + } + /* * fetch head of list before unlocking metapage. head page must be pinned * to prevent deletion by vacuum process @@ -1871,6 +2116,7 @@ scanPendingInsert(IndexScanDesc scan, TIDBitmap *tbm, int64 *ntids) pos.firstOffset = FirstOffsetNumber; UnlockReleaseBuffer(metabuffer); pos.hasMatchKey = palloc_array(bool, so->nkeys); + pos.entryResDirty = true; /* * loop for each heap row. scanGetCandidate returns full row or row's @@ -1895,6 +2141,7 @@ scanPendingInsert(IndexScanDesc scan, TIDBitmap *tbm, int64 *ntids) oldCtx = MemoryContextSwitchTo(so->tempCtx); recheck = false; match = true; + pos.entryResDirty = true; for (uint32 i = 0; i < so->nkeys; i++) { diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 656f1f60862..f17ca41f8cc 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -3754,6 +3754,7 @@ ec_member_foreign_arg ec_member_matches_arg element_type emit_log_hook_type +entrySortArg eval_const_expressions_context exec_thread_arg execution_state @@ -4002,6 +4003,7 @@ partition_method_t pe_test_config pe_test_escape_func pe_test_vector +pendingKeyState pendingPosition pending_label pgParameterStatus -- 2.37.1 (Apple Git-137.1)
From 5cab10c0b5ce82391defd6ca503b61a1d17685ce Mon Sep 17 00:00:00 2001 From: Shihao <[email protected]> Date: Wed, 23 Sep 2026 09:46:12 -0400 Subject: [PATCH v2 3/3] Add tests for GIN pending list scans with many scan entries Cover queries with 100 or more entries, including duplicate, partial match and empty query entries, and rows that do not fit in work_mem. Discussion: https://postgr.es/m/CALdSSPisYC-zHiWJU36AP-zrPN-im-gEZrK-QD8TPn3Vb8xMvw@mail.gmail.com --- src/test/regress/expected/gin.out | 83 +++++++++++++++++++++++++++++++ src/test/regress/sql/gin.sql | 41 +++++++++++++++ 2 files changed, 124 insertions(+) diff --git a/src/test/regress/expected/gin.out b/src/test/regress/expected/gin.out index 118bc26d8cd..78865c01add 100644 --- a/src/test/regress/expected/gin.out +++ b/src/test/regress/expected/gin.out @@ -288,6 +288,89 @@ select count(*) from t_gin_test_tbl where j @> '{}'::int[]; reset enable_seqscan; reset enable_bitmapscan; drop table t_gin_test_tbl; +-- Test pending list scans with scan keys that have many entries. Those +-- sort the entries and look up the tuples of each row in them. +create temp table t_gin_test_tbl(id int, i int4[], j int4[]); +create index t_gin_test_tbl_i_j_idx on t_gin_test_tbl using gin (i, j) + with (fastupdate = on, gin_pending_list_limit = 4096); +insert into t_gin_test_tbl + select g, array[g, g + 1000], array[g % 3] from generate_series(1, 100) g; +set enable_seqscan = off; +explain (costs off) +select count(*) from t_gin_test_tbl where i && array(select generate_series(1001, 1100)) and j && '{0}'; + QUERY PLAN +---------------------------------------------------------------------------------------- + Aggregate + InitPlan array_1 + -> ProjectSet + -> Result + -> Bitmap Heap Scan on t_gin_test_tbl + Recheck Cond: ((i && (InitPlan array_1).col1) AND (j && '{0}'::integer[])) + -> Bitmap Index Scan on t_gin_test_tbl_i_j_idx + Index Cond: ((i && (InitPlan array_1).col1) AND (j && '{0}'::integer[])) +(8 rows) + +-- a key with many entries next to one with few +select count(*) from t_gin_test_tbl where i && array(select generate_series(1001, 1100)) and j && '{0}'; + count +------- + 33 +(1 row) + +-- duplicate query entries +select count(*) from t_gin_test_tbl where j @> array_fill(0, array[100]); + count +------- + 33 +(1 row) + +-- each matching row has 500 true entries, so with a small work_mem only +-- some of the rows are remembered, and the rest are checked while scanning +set work_mem = '64kB'; +select count(*) from t_gin_test_tbl where j && array_fill(0, array[500]); + count +------- + 33 +(1 row) + +reset work_mem; +-- make sure the queries above scanned a pending list +select gin_clean_pending_list('t_gin_test_tbl_i_j_idx') > 0 as flushed; + flushed +--------- + t +(1 row) + +drop table t_gin_test_tbl; +-- same with partial-match and GIN_CAT_EMPTY_QUERY entries, which are +-- still checked one by one +create temp table t_gin_test_tbl(id int, v tsvector); +create index t_gin_test_tbl_v_idx on t_gin_test_tbl using gin (v) + with (fastupdate = on, gin_pending_list_limit = 4096); +insert into t_gin_test_tbl + select g, array_to_tsvector(array['a' || g, 'b' || g]) from generate_series(1, 100) g; +select count(*) from t_gin_test_tbl + where v @@ (select string_agg('b' || g, ' | ') || ' | a1:*' from generate_series(1001, 1100) g)::tsquery; + count +------- + 12 +(1 row) + +select count(*) from t_gin_test_tbl + where v @@ (select '!(' || string_agg('b' || g, ' | ') || ')' from generate_series(1, 90) g)::tsquery; + count +------- + 10 +(1 row) + +select gin_clean_pending_list('t_gin_test_tbl_v_idx') > 0 as flushed; + flushed +--------- + t +(1 row) + +reset enable_seqscan; +drop table t_gin_test_tbl; -- test an unlogged table, mostly to get coverage of ginbuildempty create unlogged table t_gin_test_tbl(i int4[], j int4[]); create index on t_gin_test_tbl using gin (i, j); diff --git a/src/test/regress/sql/gin.sql b/src/test/regress/sql/gin.sql index 0c6905ba3bb..0d01aedf64f 100644 --- a/src/test/regress/sql/gin.sql +++ b/src/test/regress/sql/gin.sql @@ -172,6 +172,47 @@ reset enable_bitmapscan; drop table t_gin_test_tbl; +-- Test pending list scans with scan keys that have many entries. Those +-- sort the entries and look up the tuples of each row in them. +create temp table t_gin_test_tbl(id int, i int4[], j int4[]); +create index t_gin_test_tbl_i_j_idx on t_gin_test_tbl using gin (i, j) + with (fastupdate = on, gin_pending_list_limit = 4096); +insert into t_gin_test_tbl + select g, array[g, g + 1000], array[g % 3] from generate_series(1, 100) g; + +set enable_seqscan = off; +explain (costs off) +select count(*) from t_gin_test_tbl where i && array(select generate_series(1001, 1100)) and j && '{0}'; +-- a key with many entries next to one with few +select count(*) from t_gin_test_tbl where i && array(select generate_series(1001, 1100)) and j && '{0}'; +-- duplicate query entries +select count(*) from t_gin_test_tbl where j @> array_fill(0, array[100]); +-- each matching row has 500 true entries, so with a small work_mem only +-- some of the rows are remembered, and the rest are checked while scanning +set work_mem = '64kB'; +select count(*) from t_gin_test_tbl where j && array_fill(0, array[500]); +reset work_mem; +-- make sure the queries above scanned a pending list +select gin_clean_pending_list('t_gin_test_tbl_i_j_idx') > 0 as flushed; + +drop table t_gin_test_tbl; + +-- same with partial-match and GIN_CAT_EMPTY_QUERY entries, which are +-- still checked one by one +create temp table t_gin_test_tbl(id int, v tsvector); +create index t_gin_test_tbl_v_idx on t_gin_test_tbl using gin (v) + with (fastupdate = on, gin_pending_list_limit = 4096); +insert into t_gin_test_tbl + select g, array_to_tsvector(array['a' || g, 'b' || g]) from generate_series(1, 100) g; +select count(*) from t_gin_test_tbl + where v @@ (select string_agg('b' || g, ' | ') || ' | a1:*' from generate_series(1001, 1100) g)::tsquery; +select count(*) from t_gin_test_tbl + where v @@ (select '!(' || string_agg('b' || g, ' | ') || ')' from generate_series(1, 90) g)::tsquery; +select gin_clean_pending_list('t_gin_test_tbl_v_idx') > 0 as flushed; + +reset enable_seqscan; +drop table t_gin_test_tbl; + -- test an unlogged table, mostly to get coverage of ginbuildempty create unlogged table t_gin_test_tbl(i int4[], j int4[]); create index on t_gin_test_tbl using gin (i, j); -- 2.37.1 (Apple Git-137.1)
