Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 950A864FD8A for ; Sun, 21 Dec 2008 21:56:26 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 05376-03 for ; Sun, 21 Dec 2008 21:56:19 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.182]) by mail.postgresql.org (Postfix) with ESMTP id 010F764FD37 for ; Sun, 21 Dec 2008 21:56:18 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id j4so1106787wah.19 for ; Sun, 21 Dec 2008 17:56:17 -0800 (PST) Received: by 10.114.103.4 with SMTP id a4mr3671320wac.91.1229910977393; Sun, 21 Dec 2008 17:56:17 -0800 (PST) Received: from ?192.168.1.10? (c-67-188-158-142.hsd1.ca.comcast.net [67.188.158.142]) by mx.google.com with ESMTPS id t1sm8943712poh.21.2008.12.21.17.56.13 (version=SSLv3 cipher=RC4-MD5); Sun, 21 Dec 2008 17:56:14 -0800 (PST) Subject: Re: [PATCHES] GIN improvements From: Jeff Davis To: Teodor Sigaev Cc: Heikki Linnakangas , Tom Lane , Pgsql Hackers In-Reply-To: <4942A137.3000502@sigaev.ru> References: <483FD205.4090904@sigaev.ru> <486BC501.6060605@sigaev.ru> <27880.1215810535@sss.pgh.pa.us> <487A6E0C.3090600@sigaev.ru> <8993.1216843701@sss.pgh.pa.us> <4888A594.6010102@sigaev.ru> <490B07BA.6030109@sigaev.ru> <492F04D9.5070404@enterprisedb.com> <4942A137.3000502@sigaev.ru> Content-Type: multipart/mixed; boundary="=-3XsaW2HftjHcEtth4W6T" Date: Sun, 21 Dec 2008 17:56:06 -0800 Message-Id: <1229910966.2285.76.camel@jdavis> Mime-Version: 1.0 X-Mailer: Evolution 2.22.3.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.944 tagged_above=0 required=5 tests=DNS_FROM_RFC_BOGUSMX=2.125, MIME_QP_LONG_LINE=1.819 X-Spam-Level: *** X-Archive-Number: 200812/1626 X-Sequence-Number: 130255 --=-3XsaW2HftjHcEtth4W6T Content-Type: text/plain Content-Transfer-Encoding: 7bit On Fri, 2008-12-12 at 20:36 +0300, Teodor Sigaev wrote: > Changes: > - added vacuum_delay_point() in gininsertcleanup > - add trigger to run vacuum by number of inserted tuples from > last vacuum. Number of inserted tuples represents number > of really inserted tuples in index and it is calculated as > tuples_inserted + tuples_updated - tuples_hot_updated. > Trigger fires on instuples > vac_base_thresh because search time is linear > on number of pending pages (tuples) Hi, Comments: 1. You use something like the following in a few places: START_CRIT_SECTION(); ... l = PageAddItem(...); if (l == InvalidOffsetNumber) elog(ERROR, "failed to add item to index page in \"%s\"", RelationGetRelationName(index)); It's no use using ERROR, because it will turn into PANIC, which is obviously unacceptable. It looks to me like those conditions can't happen anyway, so it's probably better to add a comment explaining why it can't happen, and Assert(). 2. It appears to be properly triggering autovacuum when only inserts happen, so I think that issue is solved. 3. Simple performance result with autovacuum off: create table random(i int[]); insert into random select ARRAY[(random() * 10)::int, (random() * 10)::int, (random() * 10)::int, (random() * 10)::int, (random() * 10)::int, (random() * 10)::int, (random() * 10)::int, (random() * 10)::int, (random() * 10)::int, (random() * 10)::int] from generate_series(1, 1000000); \timing on drop table foo; create table foo(i int[]); create index foogin on foo using gin (i); insert into foo select i from random; vacuum foo; Without patch: INSERT: 71s VACUUM: 2s total: 73s With patch: INSERT: 33s VACUUM: 12s total: 45s So, there is a performance advantage. This was just a quick test to make sure the numbers matched my expectations. 4. Heikki mentioned: http://archives.postgresql.org/pgsql-hackers/2008-11/msg01832.php "To make things worse, a query will fail if all the matching fast-inserted tuples don't fit in the non-lossy tid bitmap." That issue still remains, correct? Is there a resolution to that? 5. I attached a newer version merged with HEAD. 6. You defined: #define GinPageHasFullRow(page) ( GinPageGetOpaque(page)->flags & GIN_LIST_FULLROW ) But in many places you still do the same check without using that macro. The macro has only one call site, so I suggest either removing the macro entirely, or using it every place you check that flag. 7. I don't understand this chunk of code: ItemPointerData item = pos->item; if ( scanGetCandidate(scan, pos) == false || ! ItemPointerEquals(&pos->item, &item) ) elog(ERROR,"Could not process tuple"); /* XXX should not be here ! */ How can (!ItemPointerEquals(&pos->item, &item)) ever happen? And how can (scanGetCandidate(scan, pos) == false) ever happen? Should that be an Assert() instead? If those can happen during normal operation, then we need a better error message there. Regards, Jeff Davis --=-3XsaW2HftjHcEtth4W6T Content-Disposition: attachment; filename=gin-fast-insert-20081221.patch Content-Type: text/x-patch; name=gin-fast-insert-20081221.patch; charset=utf-8 Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index e34a2f9..6cce1f3 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -551,6 +551,13 @@ =20 + aminsertcleanup + regproc + pg_pro= c.oid + Post-INSERT cleanup finction (optional) + + + amvacuumcleanup regproc pg_pro= c.oid diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 7493ca9..d7236f8 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -3525,6 +3525,11 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' W= ITH csv; This setting can be overridden for individual tables by entries in pg_autovacuum. + + This parameter affects on vacuuming a table with GIN + index, it also specifies the minimum number of inserted or updated= =20 + tuples needed to trigger a VACUUM on thos table. + =20 diff --git a/doc/src/sgml/gin.sgml b/doc/src/sgml/gin.sgml index 1c5841a..adc77c4 100644 --- a/doc/src/sgml/gin.sgml +++ b/doc/src/sgml/gin.sgml @@ -188,9 +188,45 @@ list of heap pointers (PL, posting list) if the list is small enough. =20 + + GIN fast update technique + + + Updating a GIN index tends to be slow because of the + intrinsic nature of inverted indexes: inserting or updating one heap ro= w + can cause many inserts into the index (one for each key extracted + from the indexed value). As of + PostgreSQL 8.4, this problem is alleviated + by postponing most of the work until the next VACUUM. + Newly inserted index entries are temporarily stored in an unsorted list= of + pending entries. VACUUM inserts all pending entries into t= he + main GIN index data structure, + using the same bulk insert techniques used during initial index creatio= n. + This greatly improves GIN index update speed, even + counting the additional vacuum overhead. + + + + The disadvantage of this approach is that searches must scan the list + of pending entries in addition to searching the regular index, and so + a large list of pending entries will slow searches significantly. + It's recommended to use properly-configured autovacuum with tables + having GIN indexes, to keep this overhead to + reasonable levels. + + + + If consistently-fast search speed is more important than update speed, + use of pending entries can be disabled by turning off the + FASTUPDATE storage parameter for a + GIN index. See for details. + + + Partial match algorithm - =20 + GIN can support partial match queries, in which the query does not determine an exact match for one or more keys, but the possibl= e @@ -225,11 +261,18 @@ Create vs insert - In most cases, insertion into a GIN index is slow + Insertion into a GIN index can be slow due to the likelihood of many keys being inserted for each value. So, for bulk insertions into a table it is advisable to drop the GIN index and recreate it after finishing bulk insertion. + + + As of PostgreSQL 8.4, this advice is less + necessary since delayed indexing is used (see for details). But for very large update= s + it may still be best to drop and recreate the index. + =20 diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_i= ndex.sgml index 8b502e6..b75ccc9 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -265,7 +265,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name The WITH clause can specify storage parameters<= /> for indexes. Each index method can have its own set of allowed storag= e - parameters. The built-in index methods all accept a single parameter: + parameters. All built-in index methods accept this parameter: =20 @@ -292,6 +292,36 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name =20 + + GIN indexes accept an additional parameter: + + + + + + FASTUPDATE + + + This setting controls usage of the fast update technique described in + . It is a Boolean parameter: + ON enables fast update, OFF disables it. + (Alternative spellings of ON and OFF are + allowed as described in .) The + default is ON. + + + + + Turning FASTUPDATE off via ALTER INDEX preve= nts + future insertions from going into the list of pending index entries, + but does not in itself flush previous entries. You might want to do= a + VACUUM afterward to ensure the pending list is emptied. + + + + + + =20 @@ -500,6 +530,13 @@ CREATE UNIQUE INDEX title_idx ON films (title) WITH (f= illfactor =3D 70); =20 + To create a GIN index with fast update turned off: + +CREATE INDEX gin_idx ON documents_table (locations) WITH (fastupdate =3D o= ff); + + + + To create an index on the column code in the table films and have the index reside in the tablespace indexspace: diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index bee0667..952481c 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -63,6 +63,13 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ blocks. This form is much slower and requires an exclusive lock on eac= h table while it is being processed. + + + For tables with GIN indexes, VACUUM (in + any form) also completes any delayed index insertions, by moving pendin= g + index entries to the appropriate places in the main GIN ind= ex + structure. (See for more details.) + =20 diff --git a/doc/src/sgml/textsearch.sgml b/doc/src/sgml/textsearch.sgml index 1b1310c..8560c07 100644 --- a/doc/src/sgml/textsearch.sgml +++ b/doc/src/sgml/textsearch.sgml @@ -3224,7 +3224,9 @@ SELECT plainto_tsquery('supernovae stars'); - GIN indexes are about ten times slower to update than GiST + GIN indexes are moderately slower to update than GiST indexes, but + about 10 times slower if fast update support was disabled + (see for details) diff --git a/src/backend/access/gin/Makefile b/src/backend/access/gin/Makef= ile index 93442ae..99ded7a 100644 --- a/src/backend/access/gin/Makefile +++ b/src/backend/access/gin/Makefile @@ -14,6 +14,6 @@ include $(top_builddir)/src/Makefile.global =20 OBJS =3D ginutil.o gininsert.o ginxlog.o ginentrypage.o gindatapage.o \ ginbtree.o ginscan.o ginget.o ginvacuum.o ginarrayproc.o \ - ginbulk.o + ginbulk.o ginfast.o =20 include $(top_srcdir)/src/backend/common.mk diff --git a/src/backend/access/gin/ginbulk.c b/src/backend/access/gin/ginb= ulk.c index 5219e55..63b5be5 100644 --- a/src/backend/access/gin/ginbulk.c +++ b/src/backend/access/gin/ginbulk.c @@ -197,6 +197,8 @@ ginInsertRecordBA(BuildAccumulator *accum, ItemPointer = heapptr, OffsetNumber att if (nentry <=3D 0) return; =20 + Assert(ItemPointerIsValid(heapptr) && attnum >=3D FirstOffsetNumber); + i =3D nentry - 1; for (; i > 0; i >>=3D 1) nbit++; diff --git a/src/backend/access/gin/gindatapage.c b/src/backend/access/gin/= gindatapage.c index bf0651d..3c188f3 100644 --- a/src/backend/access/gin/gindatapage.c +++ b/src/backend/access/gin/gindatapage.c @@ -43,8 +43,14 @@ MergeItemPointers(ItemPointerData *dst, ItemPointerData = *a, uint32 na, ItemPoint =20 while (aptr - a < na && bptr - b < nb) { - if (compareItemPointers(aptr, bptr) > 0) + int cmp =3D compareItemPointers(aptr, bptr);=20 + if (cmp > 0) *dptr++ =3D *bptr++; + else if ( cmp =3D=3D 0 ) + { + *dptr++ =3D *bptr++; + aptr++; + } else *dptr++ =3D *aptr++; } @@ -630,11 +636,16 @@ insertItemPointer(GinPostingTreeScan *gdi, ItemPointe= rData *items, uint32 nitem) gdi->stack =3D ginFindLeafPage(&gdi->btree, gdi->stack); =20 if (gdi->btree.findItem(&(gdi->btree), gdi->stack)) - elog(ERROR, "item pointer (%u,%d) already exists", - ItemPointerGetBlockNumber(gdi->btree.items + gdi->btree.curitem), - ItemPointerGetOffsetNumber(gdi->btree.items + gdi->btree.curitem)); - - ginInsertValue(&(gdi->btree), gdi->stack); + { + /* + * gdi->btree.items[ gdi->btree.curitem ] already exists in index + */ + gdi->btree.curitem ++; + LockBuffer(gdi->stack->buffer, GIN_UNLOCK); + freeGinBtreeStack(gdi->stack); + } + else + ginInsertValue(&(gdi->btree), gdi->stack); =20 gdi->stack =3D NULL; } diff --git a/src/backend/access/gin/ginfast.c b/src/backend/access/gin/ginf= ast.c new file mode 100644 index 0000000..3ca335d --- /dev/null +++ b/src/backend/access/gin/ginfast.c @@ -0,0 +1,761 @@ +/*------------------------------------------------------------------------= - + * + * ginfast.c + * Fast insert routines for the Postgres inverted index access method. + * Pending entries are stored in linear list of pages and vacuum=20 + * will transfer them into regular structure. + * + * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * $PostgreSQL$ + * + *------------------------------------------------------------------------= - + */ + +#include "postgres.h" + +#include "access/genam.h" +#include "access/gin.h" +#include "access/tuptoaster.h" +#include "catalog/index.h" +#include "commands/vacuum.h" +#include "miscadmin.h" +#include "storage/bufmgr.h" +#include "utils/memutils.h" + + +static int32 +writeListPage(Relation index, Buffer buffer, IndexTuple *tuples, int32 ntu= ples, BlockNumber rightlink) +{ + Page page =3D BufferGetPage(buffer); + int i, freesize, size=3D0; + OffsetNumber l, off; + + START_CRIT_SECTION(); + + GinInitBuffer(buffer, GIN_LIST); + + off =3D FirstOffsetNumber; + + for(i=3D0; irightlink =3D rightlink; + /* + * tail page may contain only the whole row(s) or final + * part of row placed on previous pages + */ + if ( rightlink =3D=3D InvalidBlockNumber ) + GinPageSetFullRow(page); + + freesize =3D PageGetFreeSpace(page); + + MarkBufferDirty(buffer); + + if (!index->rd_istemp) + { + XLogRecData rdata[2]; + ginxlogInsertListPage data; + XLogRecPtr recptr; + char *ptr; + + rdata[0].buffer =3D buffer; + rdata[0].buffer_std =3D true; + rdata[0].data =3D (char*)&data; + rdata[0].len =3D sizeof(ginxlogInsertListPage); + rdata[0].next =3D rdata+1; + + rdata[1].buffer =3D InvalidBuffer; + ptr =3D rdata[1].data =3D palloc( size ); + rdata[1].len =3D size; + rdata[1].next =3D NULL; + + for(i=3D0; i 0); + + /* + * Split tuples for pages + */ + for(i=3D0;ihead =3D BufferGetBlockNumber(curBuffer); + } + + prevBuffer =3D curBuffer;; + startTuple =3D i; + size =3D 0; + } + + tupsize =3D IndexTupleSize(tuples[i]) + sizeof(ItemIdData); + + if ( size + tupsize >=3D GinListPageSize ) + { + i--; + curBuffer =3D InvalidBuffer; + } + else + { + size +=3D tupsize; + } + } + + /* + * Write last page + */ + res->tail =3D BufferGetBlockNumber(curBuffer); + res->tailFreeSize =3D writeListPage(index, curBuffer, tuples+startTuple, = ntuples-startTuple, InvalidBlockNumber); +} + +#define GIN_PAGE_FREESIZE \ + ( BLCKSZ - MAXALIGN(SizeOfPageHeaderData) - MAXALIGN(sizeof(GinPageOpaque= Data)) )=20 +/* + * Inserts collected values during normal insertion. Function guarantees + * that all values of heap will be stored sequentually with + * preserving order + */ +void +ginHeapTupleFastInsert(Relation index, GinTupleCollector *collector) +{ + Buffer metabuffer; + Page metapage; + GinMetaPageData *metadata =3D NULL; + XLogRecData rdata[2]; + Buffer buffer =3D InvalidBuffer; + Page page =3D NULL; + ginxlogUpdateMeta data; + bool separateList =3D false; + + if ( collector->ntuples =3D=3D 0 ) + return; + + data.node =3D index->rd_node; + data.ntuples =3D 0; + data.newRightlink =3D data.prevTail =3D InvalidBlockNumber; + + rdata[0].buffer =3D InvalidBuffer; + rdata[0].data =3D (char *) &data; + rdata[0].len =3D sizeof(ginxlogUpdateMeta); + rdata[0].next =3D NULL; + + metabuffer =3D ReadBuffer(index, GIN_METAPAGE_BLKNO); + metapage =3D BufferGetPage(metabuffer); + + if ( collector->sumsize + collector->ntuples * sizeof(ItemIdData) > GIN_P= AGE_FREESIZE ) + { + /* + * Total size is greater than one page =3D> make sublist + */ + separateList =3D true; + } + else + { + LockBuffer(metabuffer, GIN_EXCLUSIVE); + metadata =3D GinPageGetMeta(metapage); + + if ( metadata->head =3D=3D InvalidBlockNumber ||=20 + collector->sumsize + collector->ntuples * sizeof(ItemIdData) > metadata= ->tailFreeSize ) + { + /* + * Pending list is empty or total size is greater than freespace=20 + * on tail page =3D> make sublist + * We unlock metabuffer to keep high concurrency + */ + separateList =3D true; + LockBuffer(metabuffer, GIN_UNLOCK); + } + } + + if ( separateList ) + { + GinMetaPageData sublist; + + /* + * We should make sublist separately and append it to the tail + */ + memset( &sublist, 0, sizeof(GinMetaPageData) ); + + makeSublist(index, collector->tuples, collector->ntuples, &sublist);=20 + + /* + * metapage was unlocked, see above + */ + LockBuffer(metabuffer, GIN_EXCLUSIVE); + metadata =3D GinPageGetMeta(metapage); + + if ( metadata->head =3D=3D InvalidBlockNumber ) + { + /* + * Sublist becomes main list + */ + START_CRIT_SECTION(); + memcpy(metadata, &sublist, sizeof(GinMetaPageData) ); + memcpy(&data.metadata, &sublist, sizeof(GinMetaPageData) ); + } + else + { + /* + * merge lists + */ + + data.prevTail =3D metadata->tail; + buffer =3D ReadBuffer(index, metadata->tail); + LockBuffer(buffer, GIN_EXCLUSIVE); + page =3D BufferGetPage(buffer); + Assert(GinPageGetOpaque(page)->rightlink =3D=3D InvalidBlockNumber); + + START_CRIT_SECTION(); + + GinPageGetOpaque(page)->rightlink =3D sublist.head; + metadata->tail =3D sublist.tail; + metadata->tailFreeSize =3D sublist.tailFreeSize; + + memcpy(&data.metadata, metadata, sizeof(GinMetaPageData) ); + data.newRightlink =3D sublist.head; + + MarkBufferDirty(buffer); + } + } + else + { + /* + * Insert into tail page, metapage is already locked + */ + + OffsetNumber l, off; + int i, tupsize; + char *ptr; + + buffer =3D ReadBuffer(index, metadata->tail); + LockBuffer(buffer, GIN_EXCLUSIVE); + page =3D BufferGetPage(buffer); + off =3D (PageIsEmpty(page)) ? FirstOffsetNumber : + OffsetNumberNext(PageGetMaxOffsetNumber(page)); + + rdata[0].next =3D rdata + 1; + + rdata[1].buffer =3D buffer; + rdata[1].buffer_std =3D true; + ptr =3D rdata[1].data =3D (char *) palloc( collector->sumsize ); + rdata[1].len =3D collector->sumsize; + rdata[1].next =3D NULL; + + data.ntuples =3D collector->ntuples; + + START_CRIT_SECTION(); + + for(i=3D0; intuples; i++) + { + tupsize =3D IndexTupleSize(collector->tuples[i]); + l =3D PageAddItem(page, (Item)collector->tuples[i], tupsize, off, false= , false); + + if (l =3D=3D InvalidOffsetNumber) + elog(ERROR, "failed to add item to index page in \"%s\"", + RelationGetRelationName(index)); + + memcpy(ptr, collector->tuples[i], tupsize); + ptr+=3Dtupsize; + + off++; + } + + metadata->tailFreeSize -=3D collector->sumsize + collector->ntuples * si= zeof(ItemIdData); + memcpy(&data.metadata, metadata, sizeof(GinMetaPageData) ); + MarkBufferDirty(buffer); + } + + /* + * Make real write=20 + */ + + MarkBufferDirty(metabuffer); + if ( !index->rd_istemp ) + { + XLogRecPtr recptr; + + recptr =3D XLogInsert(RM_GIN_ID, XLOG_GIN_UPDATE_META_PAGE, rdata); + PageSetLSN(metapage, recptr); + PageSetTLI(metapage, ThisTimeLineID); + + if ( buffer !=3D InvalidBuffer ) + { + PageSetLSN(page, recptr); + PageSetTLI(page, ThisTimeLineID); + } + } + + if (buffer !=3D InvalidBuffer) + UnlockReleaseBuffer(buffer); + UnlockReleaseBuffer(metabuffer); + + END_CRIT_SECTION(); +} + +/* + * Collect values from one tuples to be indexed. All values for + * one tuples shouold be written at once - to guarantee consistent state + */ +uint32 +ginHeapTupleFastCollect(Relation index, GinState *ginstate, GinTupleCollec= tor *collector, + OffsetNumber attnum, Datum value, ItemPointer item) +{ + Datum *entries; + int32 i, + nentries; + + entries =3D extractEntriesSU(ginstate, attnum, value, &nentries); + + if (nentries =3D=3D 0) + /* nothing to insert */ + return 0; + + /* + * Allocate/reallocate memory for storing collected tuples + */ + if ( collector->tuples =3D=3D NULL ) + { + collector->lentuples =3D nentries * index->rd_att->natts; + collector->tuples =3D (IndexTuple*)palloc(sizeof(IndexTuple) * collector= ->lentuples); + } +=09 + while ( collector->ntuples + nentries > collector->lentuples ) + { + collector->lentuples *=3D 2; + collector->tuples =3D (IndexTuple*)repalloc( collector->tuples, + sizeof(IndexTuple) * collector->lentuples); + } + + /* + * Creates tuple's array + */ + for (i =3D 0; i < nentries; i++) + { + int32 tupsize; + + collector->tuples[collector->ntuples + i] =3D GinFormTuple(ginstate, att= num, entries[i], NULL, 0); + collector->tuples[collector->ntuples + i]->t_tid =3D *item; + tupsize =3D IndexTupleSize(collector->tuples[collector->ntuples + i]); + + if ( tupsize > TOAST_INDEX_TARGET || tupsize >=3D GinMaxItemSize) + elog(ERROR, "huge tuple"); + + collector->sumsize +=3D tupsize; + } + + collector->ntuples +=3D nentries; + + return nentries; +} + +/* + * Deletes first pages in list before newHead page. + * If newHead =3D=3D InvalidBlockNumber then function drops the whole list= . + * returns true if concurrent completion process is running + */ +static bool +shiftList(Relation index, Buffer metabuffer, BlockNumber newHead, + IndexBulkDeleteResult *stats) +{ +#define NDELETE_AT_ONCE (16) + Buffer buffers[NDELETE_AT_ONCE]; + ginxlogDeleteListPages data; + XLogRecData rdata[1]; + Page metapage; + GinMetaPageData *metadata; + BlockNumber blknoToDelete; + + metapage =3D BufferGetPage(metabuffer); + metadata =3D GinPageGetMeta(metapage); + blknoToDelete =3D metadata->head; + + data.node =3D index->rd_node; + + rdata[0].buffer =3D InvalidBuffer; + rdata[0].data =3D (char *) &data; + rdata[0].len =3D sizeof(ginxlogDeleteListPages); + rdata[0].next =3D NULL; + + do + { + Page page; + int i; + + data.ndeleted =3D 0; + while( data.ndeleted < NDELETE_AT_ONCE && blknoToDelete !=3D newHead ) + { + data.toDelete[ data.ndeleted ] =3D blknoToDelete;=20 + buffers[ data.ndeleted ] =3D ReadBuffer(index, blknoToDelete); + LockBufferForCleanup( buffers[ data.ndeleted ] ); + page =3D BufferGetPage( buffers[ data.ndeleted ] ); + + data.ndeleted++; + stats->pages_deleted++; + + if ( GinPageIsDeleted(page) ) + { + /* concurrent deletion process is detected */ + for(i=3D0;irightlink; + } + + START_CRIT_SECTION(); + =09 + metadata->head =3D blknoToDelete; + if ( blknoToDelete =3D=3D InvalidBlockNumber ) + { + metadata->tail =3D InvalidBlockNumber; + metadata->tailFreeSize =3D 0; + } + memcpy( &data.metadata, metadata, sizeof(GinMetaPageData)); + MarkBufferDirty( metabuffer ); + + for(i=3D0; iflags =3D GIN_DELETED; + MarkBufferDirty( buffers[ i ] ); + } + + if ( !index->rd_istemp ) + { + XLogRecPtr recptr; + + recptr =3D XLogInsert(RM_GIN_ID, XLOG_GIN_DELETE_LISTPAGE, rdata); + PageSetLSN(metapage, recptr); + PageSetTLI(metapage, ThisTimeLineID); + + for(i=3D0; invalues >=3D datums->maxvalues) + { + datums->maxvalues *=3D 2; + datums->values =3D (Datum*)repalloc( datums->values, sizeof(Datum)*datum= s->maxvalues); + } + + datums->values[ datums->nvalues++ ] =3D datum;=20 +} + +/* + * Go through all tuples on page and collect values in memory + */ + +static void=20 +processPendingPage(BuildAccumulator *accum, DatumArray *da, Page page, Off= setNumber startoff) +{ + ItemPointerData heapptr; + OffsetNumber i,maxoff; + OffsetNumber attrnum, curattnum; + + maxoff =3D PageGetMaxOffsetNumber(page); + Assert( maxoff >=3D FirstOffsetNumber ); + ItemPointerSetInvalid(&heapptr); + attrnum =3D 0; + + for (i =3D startoff; i <=3D maxoff; i =3D OffsetNumberNext(i)) + { + IndexTuple itup =3D (IndexTuple) PageGetItem(page, PageGetItemId(page, = i)); + + curattnum =3D gintuple_get_attrnum(accum->ginstate, itup); + + if ( !ItemPointerIsValid(&heapptr) ) + { + heapptr =3D itup->t_tid; + attrnum =3D curattnum; + } + else if ( !(ItemPointerEquals(&heapptr, &itup->t_tid) && curattnum =3D= =3D attrnum) ) + { + /* + * We can insert several datums per call, but only for one heap tuple + * and one column. + */ + ginInsertRecordBA(accum, &heapptr, attrnum, da->values, da->nvalues); + da->nvalues =3D 0; + heapptr =3D itup->t_tid; + attrnum =3D curattnum; + } + addDatum(da, gin_index_getattr(accum->ginstate, itup)); + } + + ginInsertRecordBA(accum, &heapptr, attrnum, da->values, da->nvalues); +} + +/* + * Moves tuples from pending pages into regular GIN structure. + * Function doesn't require special locking and could be called + * in any time but only one at the same time.=20 + */=20 + +Datum=20 +gininsertcleanup(PG_FUNCTION_ARGS) +{ + IndexVacuumInfo *info =3D (IndexVacuumInfo *) PG_GETARG_POINTER(0); + IndexBulkDeleteResult *stats =3D (IndexBulkDeleteResult *) PG_GETARG_POIN= TER(1); + Relation index =3D info->index; + GinState ginstate; + Buffer metabuffer, buffer; + Page metapage, page; + GinMetaPageData *metadata; + MemoryContext opCtx, oldCtx; + BuildAccumulator accum; + DatumArray datums; + BlockNumber blkno; + + /* Set up all-zero stats if ginbulkdelete wasn't called */ + if (stats =3D=3D NULL) + stats =3D (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult= )); + + initGinState(&ginstate, index); + + metabuffer =3D ReadBuffer(index, GIN_METAPAGE_BLKNO); + LockBuffer(metabuffer, GIN_SHARE); + metapage =3D BufferGetPage(metabuffer); + metadata =3D GinPageGetMeta(metapage); + + if ( metadata->head =3D=3D InvalidBlockNumber ) + { + UnlockReleaseBuffer(metabuffer); + PG_RETURN_POINTER(stats); + } + + /* + * Init + */ + datums.maxvalues=3D128; + datums.nvalues =3D 0; + datums.values =3D (Datum*)palloc(sizeof(Datum)*datums.maxvalues); + + ginInitBA(&accum); + accum.ginstate =3D &ginstate; + + opCtx =3D AllocSetContextCreate(CurrentMemoryContext, + "Gin refresh temporary context", + ALLOCSET_DEFAULT_MINSIZE, + ALLOCSET_DEFAULT_INITSIZE, + ALLOCSET_DEFAULT_MAXSIZE); + + oldCtx =3D MemoryContextSwitchTo(opCtx); + + /* + * Read and lock head + */ + blkno =3D metadata->head; + buffer =3D ReadBuffer(index, blkno); + LockBuffer(buffer, GIN_SHARE); + page =3D BufferGetPage(buffer); + + LockBuffer(metabuffer, GIN_UNLOCK); + + for(;;) + { + /* + * reset datum's collector and read page's datums into memory + */ + datums.nvalues =3D 0; + + if ( GinPageIsDeleted(page) ) + { + /* concurrent completion is running */ + UnlockReleaseBuffer( buffer ); + break; + } + + processPendingPage(&accum, &datums, page, FirstOffsetNumber); + + vacuum_delay_point(); + + /* + * Is it time to flush memory to disk? + */ + if ( GinPageGetOpaque(page)->rightlink =3D=3D InvalidBlockNumber ||=20 + ( GinPageHasFullRow(page) && accum.allocatedMemory > maintenance_work_= mem * 1024L ) ) + { + ItemPointerData *list; + uint32 nlist; + Datum entry; + OffsetNumber maxoff, attnum; + + /* + * Unlock current page to increase performance. + * Changes of page will be checked later by comparing=20 + * maxoff after completion of memory flush.=20 + */ + maxoff =3D PageGetMaxOffsetNumber(page); + LockBuffer(buffer, GIN_UNLOCK); + + /* + * Moving collected data into regular structure can take + * significant amount of time - so, run it without locking pending + * list. + */ + while ((list =3D ginGetEntry(&accum, &attnum, &entry, &nlist)) !=3D NUL= L) + { + vacuum_delay_point(); + ginEntryInsert(index, &ginstate, attnum, entry, list, nlist, FALSE); + } + + /* + * Lock the whole list to remove pages=20 + */ + LockBuffer(metabuffer, GIN_EXCLUSIVE); + LockBuffer(buffer, GIN_SHARE); + + if ( GinPageIsDeleted(page) ) + { + /* concurrent completion is running */ + UnlockReleaseBuffer(buffer); + LockBuffer(metabuffer, GIN_UNLOCK); + break; + } + + /* + * While we keeped page unlocked it might be changed -=20 + * add read the changes separately. On one page is rather + * small - so, overused memory isn't very big, although=20 + * we should reinit accumulator. We need to make a + * check only once because now both page and metapage are + * locked. Inserion algorithm gurantees that inserted row(s) + * will not continue on next page. + */ + if ( PageGetMaxOffsetNumber(page) !=3D maxoff ) + { + ginInitBA(&accum); + datums.nvalues =3D 0; + processPendingPage(&accum, &datums, page, maxoff+1); + + while ((list =3D ginGetEntry(&accum, &attnum, &entry, &nlist)) !=3D NU= LL) + ginEntryInsert(index, &ginstate, attnum, entry, list, nlist, FALSE); + } + + /* + * Remember next page - it will become a new head + */ + blkno =3D GinPageGetOpaque(page)->rightlink; + UnlockReleaseBuffer(buffer); /* shiftList will do exclusive locking */ + + /* + * remove readed pages from pending list, at this point all=20 + * content of readed pages is in regular structure + */ + if ( shiftList(index, metabuffer, blkno, stats) ) + { + /* concurrent completion is running */ + LockBuffer(metabuffer, GIN_UNLOCK); + break; + } + + Assert( blkno =3D=3D metadata->head ); + LockBuffer(metabuffer, GIN_UNLOCK); + + /* + * if we remove the whole list just exit=20 + */ + if ( blkno =3D=3D InvalidBlockNumber ) + break; + + /* + * reinit state + */ + MemoryContextReset(opCtx); + ginInitBA(&accum); + } + else + { + blkno =3D GinPageGetOpaque(page)->rightlink; + UnlockReleaseBuffer(buffer); + } + + + /* + * Read next page in pending list + */ + CHECK_FOR_INTERRUPTS(); + buffer =3D ReadBuffer(index, blkno); + LockBuffer(buffer, GIN_SHARE); + page =3D BufferGetPage(buffer); + } + + ReleaseBuffer(metabuffer); + MemoryContextSwitchTo(oldCtx); + MemoryContextDelete(opCtx); + + PG_RETURN_POINTER(stats); +} diff --git a/src/backend/access/gin/ginget.c b/src/backend/access/gin/ginge= t.c index 23131e5..69c15fc 100644 --- a/src/backend/access/gin/ginget.c +++ b/src/backend/access/gin/ginget.c @@ -268,6 +268,15 @@ startScanEntry(Relation index, GinState *ginstate, Gin= ScanEntry entry) Page page; bool needUnlock =3D TRUE; =20 + entry->buffer =3D InvalidBuffer; + entry->offset =3D InvalidOffsetNumber; + entry->list =3D NULL; + entry->nlist =3D 0; + entry->partialMatch =3D NULL; + entry->partialMatchResult =3D NULL; + entry->reduceResult =3D FALSE; + entry->predictNumberResult =3D 0; + if (entry->master !=3D NULL) { entry->isFinished =3D entry->master->isFinished; @@ -285,14 +294,6 @@ startScanEntry(Relation index, GinState *ginstate, Gin= ScanEntry entry) page =3D BufferGetPage(stackEntry->buffer); =20 entry->isFinished =3D TRUE; - entry->buffer =3D InvalidBuffer; - entry->offset =3D InvalidOffsetNumber; - entry->list =3D NULL; - entry->nlist =3D 0; - entry->partialMatch =3D NULL; - entry->partialMatchResult =3D NULL; - entry->reduceResult =3D FALSE; - entry->predictNumberResult =3D 0; =20 if ( entry->isPartialMatch ) { @@ -350,9 +351,10 @@ startScanEntry(Relation index, GinState *ginstate, Gin= ScanEntry entry) =20 entry->buffer =3D scanBeginPostingTree(gdi); /* - * We keep buffer pinned because we need to prevent deletition + * We keep buffer pinned because we need to prevent deletion of * page during scan. See GIN's vacuum implementation. RefCount - * is increased to keep buffer pinned after freeGinBtreeStack() call. + * is increased to keep buffer pinned after freeGinBtreeStack() + * call. */ IncrBufferRefCount(entry->buffer); =20 @@ -429,6 +431,15 @@ startScan(IndexScanDesc scan) uint32 i; GinScanOpaque so =3D (GinScanOpaque) scan->opaque; =20 + /* + * If isScanFastInsert is still true, set up to scan the pending-insert + * list rather than the main index. + */ + if (so->isScanFastInsert) + { + return; + } + for (i =3D 0; i < so->nkeys; i++) startScanKey(scan->indexRelation, &so->ginstate, so->keys + i); } @@ -671,6 +682,336 @@ keyGetItem(Relation index, GinState *ginstate, Memory= Context tempCtx, return FALSE; } =20 +typedef struct fastPosition { + Buffer fastBuffer; + OffsetNumber firstOffset; + OffsetNumber lastOffset; + ItemPointerData item; +} fastPosition; + + +/* + * Get ItemPointer of next heap row to be checked from fast insert storage= . + * Returns false if there are no more. + * + * The fastBuffer is presumed pinned and share-locked on entry, and is=20 + * pinned and share-locked on success exit. On failure exit it's released= . + */ +static bool +scanGetCandidate(IndexScanDesc scan, fastPosition *pos) +{ + OffsetNumber maxoff; + Page page; + IndexTuple itup; + + ItemPointerSetInvalid( &pos->item ); + for(;;) + { + page =3D BufferGetPage(pos->fastBuffer); + + maxoff =3D PageGetMaxOffsetNumber(page); + if ( pos->firstOffset > maxoff ) + { + BlockNumber blkno =3D GinPageGetOpaque(page)->rightlink; + if ( blkno =3D=3D InvalidBlockNumber ) + { + UnlockReleaseBuffer(pos->fastBuffer); + pos->fastBuffer=3DInvalidBuffer; + + return false; + } + else + { + /* + * Here we should prevent deletion of next page by=20 + * insertcleanup process, which uses LockBufferForCleanup. + * So, we pin next page before unpin current one + */ + Buffer tmpbuf =3D ReadBuffer(scan->indexRelation, blkno); + + UnlockReleaseBuffer( pos->fastBuffer); + pos->fastBuffer=3Dtmpbuf; + LockBuffer( pos->fastBuffer, GIN_SHARE ); + + pos->firstOffset =3D FirstOffsetNumber; + } + } + else + { + itup =3D (IndexTuple) PageGetItem(page, PageGetItemId(page, pos->firstO= ffset)); + pos->item =3D itup->t_tid; + if ( GinPageGetOpaque(page)->flags & GIN_LIST_FULLROW ) + { + /* + * find itempointer to the next row + */ + for(pos->lastOffset =3D pos->firstOffset+1; pos->lastOffset<=3Dmaxoff;= pos->lastOffset++) + { + itup =3D (IndexTuple) PageGetItem(page, PageGetItemId(page, pos->last= Offset)); + if (!ItemPointerEquals(&pos->item, &itup->t_tid)) + break; + } + } + else + { + /* + * All itempointers are the same on this page + */ + pos->lastOffset =3D maxoff + 1; + } + break; + } + } + + return true; +} + +static bool +matchPartialInPendingList(GinState *ginstate, Page page, OffsetNumber off,= =20 + OffsetNumber maxoff, Datum value, OffsetNumber attrnum, + Datum *datum, bool *datumExtracted, StrategyNumber strategy) +{ + IndexTuple itup; + int res; + + while( off < maxoff ) + { + itup =3D (IndexTuple) PageGetItem(page, PageGetItemId(page, off)); + if ( attrnum !=3D gintuple_get_attrnum(ginstate, itup) ) + return false; + =09 + if (datumExtracted[ off-1 ] =3D=3D false) + { + datum[ off-1 ] =3D gin_index_getattr(ginstate, itup); + datumExtracted[ off-1 ] =3D true; + } + + res =3D DatumGetInt32(FunctionCall3(&ginstate->comparePartialFn[attrnum]= , + value, + datum[ off-1 ], + UInt16GetDatum(strategy))); + if ( res =3D=3D 0 ) + return true; + else if (res>0) + return false; + } + + return false; +} +/* + * Sets entryRes array for each key by looking on + * every entry per indexed value (row) in fast insert storage. + * returns true if at least one of datum was matched by key's entry + * + * The fastBuffer is presumed pinned and share-locked on entry. + */ +static bool +collectDatumForItem(IndexScanDesc scan, fastPosition *pos) +{ + GinScanOpaque so =3D (GinScanOpaque) scan->opaque; + OffsetNumber attrnum; + Page page; + IndexTuple itup; + int i, j; + bool hasMatch =3D false; + + /* + * Resets entryRes + */ + for (i =3D 0; i < so->nkeys; i++) + { + GinScanKey key =3D so->keys + i; + memset( key->entryRes, FALSE, key->nentries ); + } + + for(;;) + { + Datum datum[ BLCKSZ/sizeof(IndexTupleData) ]; + bool datumExtracted[ BLCKSZ/sizeof(IndexTupleData) ]; + + Assert( pos->lastOffset > pos->firstOffset ); + memset(datumExtracted + pos->firstOffset - 1, 0, sizeof(bool) * (pos->la= stOffset - pos->firstOffset )); + + page =3D BufferGetPage(pos->fastBuffer); + + for(i =3D 0; i < so->nkeys; i++) + { + GinScanKey key =3D so->keys + i; + + for(j=3D0; jnentries; j++) + { + OffsetNumber StopLow =3D pos->firstOffset, + StopHigh =3D pos->lastOffset, + StopMiddle; + GinScanEntry entry =3D key->scanEntry + j; + + if ( key->entryRes[j] ) + continue; + + while (StopLow < StopHigh) + { + StopMiddle =3D StopLow + ((StopHigh - StopLow) >> 1); + + itup =3D (IndexTuple) PageGetItem(page, PageGetItemId(page, StopMiddl= e)); + attrnum =3D gintuple_get_attrnum(&so->ginstate, itup); + + if (key->attnum < attrnum) + StopHigh =3D StopMiddle; + else if (key->attnum > attrnum) + StopLow =3D StopMiddle + 1; + else + { + int res; + + if (datumExtracted[ StopMiddle-1 ] =3D=3D false) + { + datum[ StopMiddle-1 ] =3D gin_index_getattr(&so->ginstate, itup); + datumExtracted[ StopMiddle-1 ] =3D true; + } + res =3D compareEntries(&so->ginstate,=20 + entry->attnum,=20 + entry->entry,=20 + datum[ StopMiddle-1 ]); + + if ( res =3D=3D 0 ) + { + if ( entry->isPartialMatch ) + key->entryRes[j] =3D matchPartialInPendingList(&so->ginstate, page= , StopMiddle,=20 + pos->lastOffset, entry->entry, entry->attnum, + datum, datumExtracted, entry->strategy); + else + key->entryRes[j] =3D true; + break; + } + else if ( res < 0 ) + StopHigh =3D StopMiddle; + else + StopLow =3D StopMiddle + 1; + } + } + + if ( StopLow>=3DStopHigh && entry->isPartialMatch ) + key->entryRes[j] =3D matchPartialInPendingList(&so->ginstate, page, S= topHigh,=20 + pos->lastOffset, entry->entry, entry->attnum, + datum, datumExtracted, entry->strategy); + + hasMatch |=3D key->entryRes[j]; + } + } +=09 + pos->firstOffset =3D pos->lastOffset; + + if ( GinPageGetOpaque(page)->flags & GIN_LIST_FULLROW ) + { + /* + * We scan all values from one tuple, go to next one + */ + + return hasMatch; + } + else + { + ItemPointerData item =3D pos->item; + + if ( scanGetCandidate(scan, pos) =3D=3D false || !ItemPointerEquals(&po= s->item, &item) ) + elog(ERROR,"Could not process tuple"); /* XXX should not be here ! */ + } + } + + return hasMatch; +} + +/* + * Collect all matched rows from pending list in bitmap + */ +static TIDBitmap* +scanFastInsert(IndexScanDesc scan) +{ + GinScanOpaque so =3D (GinScanOpaque) scan->opaque; + MemoryContext oldCtx; + bool recheck, keyrecheck, match; + TIDBitmap *tbm =3D NULL; + int i; + fastPosition pos; + Buffer metabuffer =3D ReadBuffer(scan->indexRelation, GIN_METAPAGE_= BLKNO); + BlockNumber blkno; + + LockBuffer(metabuffer, GIN_SHARE); + blkno =3D GinPageGetMeta(BufferGetPage(metabuffer))->head; + + /* + * fetch head of list before unlocking metapage. + * head page must be pinned to prevent deletion by vacuum process + */ + if ( blkno =3D=3D InvalidBlockNumber ) + { + /* No pending list, so proceed with normal scan */ + UnlockReleaseBuffer( metabuffer ); + return NULL; + } + + pos.fastBuffer =3D ReadBuffer(scan->indexRelation, blkno); + LockBuffer(pos.fastBuffer, GIN_SHARE); + pos.firstOffset =3D FirstOffsetNumber; + UnlockReleaseBuffer( metabuffer ); + + /* + * loop for each heap row + */ + while( scanGetCandidate(scan, &pos) ) + { + + /* + * Check entries in rows and setup entryRes array + */ + if (!collectDatumForItem(scan, &pos)) + continue; + + /* + * check for consistent + */ + oldCtx =3D MemoryContextSwitchTo(so->tempCtx); + recheck =3D false; + match =3D true;=20 + + for (i =3D 0; match && i < so->nkeys; i++) + { + GinScanKey key =3D so->keys + i; + + keyrecheck =3D true; + + if ( DatumGetBool(FunctionCall4(&so->ginstate.consistentFn[ key->attnum= -1 ], + PointerGetDatum(key->entryRes), + UInt16GetDatum(key->strategy), + key->query, + PointerGetDatum(&keyrecheck))) =3D=3D false ) + { + match =3D false; + } + + recheck |=3D keyrecheck; + } + + MemoryContextSwitchTo(oldCtx); + MemoryContextReset(so->tempCtx); + + if ( match ) + { + if ( tbm =3D=3D NULL ) + tbm =3D tbm_create( work_mem * 1024L ); + tbm_add_tuples(tbm, &pos.item, 1, recheck); + } + } + + if ( tbm && tbm_has_lossy(tbm) )=09 + ereport(ERROR, + (errcode(ERRCODE_OUT_OF_MEMORY), + errmsg("not enough memory to store result of pending list or VACUUME = table" ), + errhint("Increase the \"work_mem\" parameter."))); + + return tbm; +} + /* * Get heap item pointer from scan * returns true if found @@ -693,44 +1034,112 @@ scanGetItem(IndexScanDesc scan, ItemPointerData *it= em, bool *recheck) */ *recheck =3D false; =20 - ItemPointerSetMin(item); - for (i =3D 0; i < so->nkeys; i++) + /* + * First of all we should check fast insert list of pages + */ + if ( so->isScanFastInsert ) { - GinScanKey key =3D so->keys + i; + if ( so->scanFastTuples ) + { + /* + * Items from pending list is already collected in memory + */ =20 - if (keyGetItem(scan->indexRelation, &so->ginstate, so->tempCtx, - key, &keyrecheck)) - return FALSE; /* finished one of keys */ - if (compareItemPointers(item, &key->curItem) < 0) - *item =3D key->curItem; - *recheck |=3D keyrecheck; - } + if ( so->scanFastResult =3D=3D NULL || so->scanFastOffset >=3D so->scan= FastResult->ntuples ) + { + so->scanFastResult =3D tbm_iterate( so->scanFastTuples ); =20 - for (i =3D 1; i <=3D so->nkeys; i++) - { - GinScanKey key =3D so->keys + i - 1; + if ( so->scanFastResult =3D=3D NULL ) + { + /* scan of pending pages is finished */ + so->isScanFastInsert =3D false; + startScan(scan); + return scanGetItem(scan, item, recheck); + } + Assert( so->scanFastResult->ntuples >=3D 0 ); + so->scanFastOffset =3D 0; + } +=09 + ItemPointerSet(item, + so->scanFastResult->blockno, + so->scanFastResult->offsets[ so->scanFastOffset ]); + *recheck =3D true; /* be conserative due to concurrent=20 + removal from pending list */ =20 - for (;;) + so->scanFastOffset ++; + + return true; + }=20 + else { - int cmp =3D compareItemPointers(item, &key->curItem); + /* + * Collect ItemPointers in memory + */ + so->scanFastTuples =3D scanFastInsert(scan); =20 - if (cmp =3D=3D 0) - break; - else if (cmp > 0) + if ( so->scanFastTuples =3D=3D NULL ) { - if (keyGetItem(scan->indexRelation, &so->ginstate, so->tempCtx, - key, &keyrecheck)) - return FALSE; /* finished one of keys */ - *recheck |=3D keyrecheck; + /* nothing found */ + so->isScanFastInsert =3D false; + startScan(scan); } else - { /* returns to begin */ + { + tbm_begin_iterate(so->scanFastTuples); + } + + return scanGetItem(scan, item, recheck); + } + } + + /* + * Regular scanning with filtering by already returned + * ItemPointers from pending list + */ + + do + { + ItemPointerSetMin(item); + *recheck =3D false; + + for (i =3D 0; i < so->nkeys; i++) + { + GinScanKey key =3D so->keys + i; + + if (keyGetItem(scan->indexRelation, &so->ginstate, so->tempCtx, + key, &keyrecheck)) + return FALSE; /* finished one of keys */ + if (compareItemPointers(item, &key->curItem) < 0) *item =3D key->curItem; - i =3D 0; + *recheck |=3D keyrecheck; + } + + for (i =3D 1; i <=3D so->nkeys; i++) + { + GinScanKey key =3D so->keys + i - 1; + + for (;;) + { + int cmp =3D compareItemPointers(item, &key->curItem); +=09 + if (cmp =3D=3D 0) break; + else if (cmp > 0) + { + if (keyGetItem(scan->indexRelation, &so->ginstate, so->tempCtx, + key, &keyrecheck)) + return FALSE; /* finished one of keys */ + *recheck |=3D keyrecheck; + } + else + { /* returns to begin */ + *item =3D key->curItem; + i =3D 0; + break; + } } } - } + } while( so->scanFastTuples && tbm_check_tuple(so->scanFastTuples, item) = ); =20 return TRUE; } diff --git a/src/backend/access/gin/gininsert.c b/src/backend/access/gin/gi= ninsert.c index 4be89bc..062ddba 100644 --- a/src/backend/access/gin/gininsert.c +++ b/src/backend/access/gin/gininsert.c @@ -138,7 +138,7 @@ addItemPointersToTuple(Relation index, GinState *ginsta= te, GinBtreeStack *stack, /* * Inserts only one entry to the index, but it can add more than 1 ItemPoi= nter. */ -static void +void ginEntryInsert(Relation index, GinState *ginstate, OffsetNumber attnum, Da= tum value,=20 ItemPointerData *items, uint32 nitem, bool isBuild) { @@ -273,7 +273,7 @@ ginbuild(PG_FUNCTION_ARGS) IndexBuildResult *result; double reltuples; GinBuildState buildstate; - Buffer buffer; + Buffer RootBuffer, MetaBuffer; ItemPointerData *list; Datum entry; uint32 nlist; @@ -286,11 +286,17 @@ ginbuild(PG_FUNCTION_ARGS) =20 initGinState(&buildstate.ginstate, index); =20 + /* initialize the meta page */ + MetaBuffer =3D GinNewBuffer(index); + /* initialize the root page */ - buffer =3D GinNewBuffer(index); + RootBuffer =3D GinNewBuffer(index); + START_CRIT_SECTION(); - GinInitBuffer(buffer, GIN_LEAF); - MarkBufferDirty(buffer); + GinInitMetabuffer(MetaBuffer); + MarkBufferDirty(MetaBuffer); + GinInitBuffer(RootBuffer, GIN_LEAF); + MarkBufferDirty(RootBuffer); =20 if (!index->rd_istemp) { @@ -303,16 +309,19 @@ ginbuild(PG_FUNCTION_ARGS) rdata.len =3D sizeof(RelFileNode); rdata.next =3D NULL; =20 - page =3D BufferGetPage(buffer); - - recptr =3D XLogInsert(RM_GIN_ID, XLOG_GIN_CREATE_INDEX, &rdata); + =09 + page =3D BufferGetPage(RootBuffer); PageSetLSN(page, recptr); PageSetTLI(page, ThisTimeLineID); =20 + page =3D BufferGetPage(MetaBuffer); + PageSetLSN(page, recptr); + PageSetTLI(page, ThisTimeLineID); } =20 - UnlockReleaseBuffer(buffer); + UnlockReleaseBuffer(MetaBuffer); + UnlockReleaseBuffer(RootBuffer); END_CRIT_SECTION(); =20 /* build the index */ @@ -417,9 +426,26 @@ gininsert(PG_FUNCTION_ARGS) =20 initGinState(&ginstate, index); =20 - for(i=3D0; inatts;i++) - if ( !isnull[i] ) - res +=3D ginHeapTupleInsert(index, &ginstate, (OffsetNumber)(i+1), valu= es[i], ht_ctid); + if ( GinGetUseFastUpdate(index) ) + { + GinTupleCollector collector; + + memset(&collector, 0, sizeof(GinTupleCollector)); + for(i=3D0; inatts;i++) + if ( !isnull[i] ) + res +=3D ginHeapTupleFastCollect(index, &ginstate, &collector, + (OffsetNumber)(i+1), values[i], ht_ctid); + + ginHeapTupleFastInsert(index, &collector); + } + else + { + for(i=3D0; inatts;i++) + if ( !isnull[i] )=20 + res +=3D ginHeapTupleInsert(index, &ginstate,=20 + (OffsetNumber)(i+1), values[i], ht_ctid); + + } =20 MemoryContextSwitchTo(oldCtx); MemoryContextDelete(insertCtx); diff --git a/src/backend/access/gin/ginscan.c b/src/backend/access/gin/gins= can.c index bc51e94..0c0ce52 100644 --- a/src/backend/access/gin/ginscan.c +++ b/src/backend/access/gin/ginscan.c @@ -159,6 +159,9 @@ newScanKey(IndexScanDesc scan) errmsg("GIN indexes do not support whole-index scans"))); =20 so->isVoidRes =3D false; + so->isScanFastInsert =3D true; + so->scanFastTuples =3D NULL; + so->scanFastResult =3D NULL; =20 for (i =3D 0; i < scan->numberOfKeys; i++) { @@ -233,8 +236,11 @@ ginrescan(PG_FUNCTION_ARGS) else { freeScanKeys(so->keys, so->nkeys); + if ( so->scanFastTuples ) + tbm_free( so->scanFastTuples ); } =20 + so->scanFastTuples =3D NULL; so->keys =3D NULL; =20 if (scankey && scan->numberOfKeys > 0) @@ -256,6 +262,8 @@ ginendscan(PG_FUNCTION_ARGS) if (so !=3D NULL) { freeScanKeys(so->keys, so->nkeys); + if ( so->scanFastTuples ) + tbm_free( so->scanFastTuples ); =20 MemoryContextDelete(so->tempCtx); =20 diff --git a/src/backend/access/gin/ginutil.c b/src/backend/access/gin/ginu= til.c index 5e71c85..6633dce 100644 --- a/src/backend/access/gin/ginutil.c +++ b/src/backend/access/gin/ginutil.c @@ -21,6 +21,7 @@ #include "storage/freespace.h" #include "storage/indexfsm.h" #include "storage/lmgr.h" +#include "utils/guc.h" =20 void initGinState(GinState *state, Relation index) @@ -57,7 +58,7 @@ initGinState(GinState *state, Relation index) CurrentMemoryContext); =20 /* - * Check opclass capability to do partial match.=20 + * Check opclass capability to do partial match. */ if ( index_getprocid(index, i+1, GIN_COMPARE_PARTIAL_PROC) !=3D InvalidO= id ) { @@ -88,7 +89,7 @@ gintuple_get_attrnum(GinState *ginstate, IndexTuple tuple= ) bool isnull; =20 /* - * First attribute is always int16, so we can safely use any=20 + * First attribute is always int16, so we can safely use any * tuple descriptor to obtain first attribute of tuple */ res =3D index_getattr(tuple, FirstOffsetNumber, ginstate->tupdesc[0], @@ -213,6 +214,20 @@ GinInitBuffer(Buffer b, uint32 f) GinInitPage(BufferGetPage(b), f, BufferGetPageSize(b)); } =20 +void +GinInitMetabuffer(Buffer b) +{ + GinMetaPageData *metadata; + Page page =3D BufferGetPage(b); + + GinInitPage(page, GIN_META, BufferGetPageSize(b)); + + metadata =3D GinPageGetMeta(page); + + metadata->head =3D metadata->tail =3D InvalidBlockNumber; + metadata->tailFreeSize =3D 0; +} + int compareEntries(GinState *ginstate, OffsetNumber attnum, Datum a, Datum b) { @@ -310,12 +325,10 @@ extractEntriesSU(GinState *ginstate, OffsetNumber att= num, Datum value, int32 *ne return entries; } =20 -Datum -ginoptions(PG_FUNCTION_ARGS) +static int +parseFillfactor(char *value, bool validate) { - Datum reloptions =3D PG_GETARG_DATUM(0); - bool validate =3D PG_GETARG_BOOL(1); - bytea *result; + int fillfactor; =20 /* * It's not clear that fillfactor is useful for GIN, but for the moment @@ -324,10 +337,73 @@ ginoptions(PG_FUNCTION_ARGS) #define GIN_MIN_FILLFACTOR 10 #define GIN_DEFAULT_FILLFACTOR 100 =20 - result =3D default_reloptions(reloptions, validate, - GIN_MIN_FILLFACTOR, - GIN_DEFAULT_FILLFACTOR); - if (result) - PG_RETURN_BYTEA_P(result); - PG_RETURN_NULL(); + if (value =3D=3D NULL) + return GIN_DEFAULT_FILLFACTOR; + + if (!parse_int(value, &fillfactor, 0, NULL)) + { + if (validate) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("fillfactor must be an integer: \"%s\"", + value))); + return GIN_DEFAULT_FILLFACTOR; + } + + if (fillfactor < GIN_MIN_FILLFACTOR || fillfactor > 100) + { + if (validate) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("fillfactor=3D%d is out of range (should be between %d and 10= 0)", + fillfactor, GIN_MIN_FILLFACTOR))); + return GIN_DEFAULT_FILLFACTOR; + } + + return fillfactor; +} + +static bool +parseFastupdate(char *value, bool validate) +{ + bool result; + + if (value =3D=3D NULL) + return GIN_DEFAULT_USE_FASTUPDATE; + + if (!parse_bool(value, &result)) + { + if (validate) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("fastupdate=3D\"%s\" is not recognized", + value))); + return GIN_DEFAULT_USE_FASTUPDATE; + } + + return result; +} + +Datum +ginoptions(PG_FUNCTION_ARGS) +{ + Datum reloptions =3D PG_GETARG_DATUM(0); + bool validate =3D PG_GETARG_BOOL(1); + static const char *const gin_keywords[2] =3D {"fillfactor", "fastupdate"}= ; + char *values[2]; + GinOptions *options; + + parseRelOptions(reloptions, 2, gin_keywords, values, validate); + + /* If no options, just return NULL */ + if (values[0] =3D=3D NULL && values[1] =3D=3D NULL) + PG_RETURN_NULL(); + + options =3D (GinOptions *) palloc(sizeof(GinOptions)); + SET_VARSIZE(options, sizeof(GinOptions)); + + options->std.fillfactor =3D parseFillfactor(values[0], validate); + options->useFastUpdate =3D parseFastupdate(values[1], validate); + + PG_RETURN_BYTEA_P(options); } diff --git a/src/backend/access/gin/ginvacuum.c b/src/backend/access/gin/gi= nvacuum.c index b180cd7..4146995 100644 --- a/src/backend/access/gin/ginvacuum.c +++ b/src/backend/access/gin/ginvacuum.c @@ -595,7 +595,14 @@ ginbulkdelete(PG_FUNCTION_ARGS) =20 /* first time through? */ if (stats =3D=3D NULL) - stats =3D (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult= )); + { + stats =3D (IndexBulkDeleteResult *)DatumGetPointer( + DirectFunctionCall2(gininsertcleanup, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1) + )); + } + /* we'll re-count the tuples each time */ stats->num_index_tuples =3D 0; =20 @@ -703,9 +710,18 @@ ginvacuumcleanup(PG_FUNCTION_ARGS) BlockNumber lastBlock =3D GIN_ROOT_BLKNO, lastFilledBlock =3D GIN_ROOT_BLKNO; =20 - /* Set up all-zero stats if ginbulkdelete wasn't called */ + /*=20 + * Set up all-zero stats and finalyze fast insertion=20 + * if ginbulkdelete wasn't called=20 + */ if (stats =3D=3D NULL) - stats =3D (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult= )); + { + stats =3D (IndexBulkDeleteResult *)DatumGetPointer( + DirectFunctionCall2(gininsertcleanup, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1) + )); + } =20 /* * XXX we always report the heap tuple count as the number of index diff --git a/src/backend/access/gin/ginxlog.c b/src/backend/access/gin/ginx= log.c index 0d40bfb..76db49c 100644 --- a/src/backend/access/gin/ginxlog.c +++ b/src/backend/access/gin/ginxlog.c @@ -71,20 +71,30 @@ static void ginRedoCreateIndex(XLogRecPtr lsn, XLogRecord *record) { RelFileNode *node =3D (RelFileNode *) XLogRecGetData(record); - Buffer buffer; + Buffer RootBuffer, MetaBuffer; Page page; =20 - buffer =3D XLogReadBuffer(*node, GIN_ROOT_BLKNO, true); - Assert(BufferIsValid(buffer)); - page =3D (Page) BufferGetPage(buffer); + MetaBuffer =3D XLogReadBuffer(*node, GIN_METAPAGE_BLKNO, true); + Assert(BufferIsValid(MetaBuffer)); + GinInitMetabuffer(MetaBuffer); + + page =3D (Page) BufferGetPage(MetaBuffer); + PageSetLSN(page, lsn); + PageSetTLI(page, ThisTimeLineID); =20 - GinInitBuffer(buffer, GIN_LEAF); + RootBuffer =3D XLogReadBuffer(*node, GIN_ROOT_BLKNO, true); + Assert(BufferIsValid(RootBuffer)); + page =3D (Page) BufferGetPage(RootBuffer); + + GinInitBuffer(RootBuffer, GIN_LEAF); =20 PageSetLSN(page, lsn); PageSetTLI(page, ThisTimeLineID); =20 - MarkBufferDirty(buffer); - UnlockReleaseBuffer(buffer); + MarkBufferDirty(MetaBuffer); + UnlockReleaseBuffer(MetaBuffer); + MarkBufferDirty(RootBuffer); + UnlockReleaseBuffer(RootBuffer); } =20 static void @@ -433,6 +443,161 @@ ginRedoDeletePage(XLogRecPtr lsn, XLogRecord *record) } } =20 +static void +ginRedoUpdateMetapage(XLogRecPtr lsn, XLogRecord *record) +{ + ginxlogUpdateMeta *data =3D (ginxlogUpdateMeta*) XLogRecGetData(record); + Buffer metabuffer; + Page metapage; + + metabuffer =3D XLogReadBuffer(data->node, GIN_METAPAGE_BLKNO, false); + metapage =3D BufferGetPage(metabuffer); + + if (!XLByteLE(lsn, PageGetLSN(metapage))) + { + memcpy( GinPageGetMeta(metapage), &data->metadata, sizeof(GinMetaPageDat= a)); + PageSetLSN(metapage, lsn); + PageSetTLI(metapage, ThisTimeLineID); + MarkBufferDirty(metabuffer); + } + + if ( data->ntuples > 0 ) + { + /* + * insert into tail page + */ + if (!(record->xl_info & XLR_BKP_BLOCK_1)) + { + Buffer buffer =3D XLogReadBuffer(data->node, data->metadata.tail, fals= e); + Page page =3D BufferGetPage(buffer); + + if ( !XLByteLE(lsn, PageGetLSN(page))) + { + OffsetNumber l, off =3D (PageIsEmpty(page)) ? FirstOffsetNumber : + OffsetNumberNext(PageGetMaxOffsetNumber(page)); + int i, tupsize; + IndexTuple tuples =3D (IndexTuple) (XLogRecGetData(record) + sizeof(g= inxlogUpdateMeta)); + + for(i=3D0; intuples; i++) + { + tupsize =3D IndexTupleSize(tuples); + + l =3D PageAddItem(page, (Item)tuples, tupsize, off, false, false); + + if (l =3D=3D InvalidOffsetNumber) + elog(ERROR, "failed to add item to index page"); + + tuples =3D (IndexTuple)( ((char*)tuples) + tupsize ); + } + + PageSetLSN(page, lsn); + PageSetTLI(page, ThisTimeLineID); + MarkBufferDirty(buffer); + } + UnlockReleaseBuffer(buffer); + } + } + else if ( data->prevTail !=3D InvalidBlockNumber ) + { + /* + * New tail + */ + + Buffer buffer =3D XLogReadBuffer(data->node, data->prevTail, false); + Page page =3D BufferGetPage(buffer); + + if ( !XLByteLE(lsn, PageGetLSN(page))) + { + GinPageGetOpaque(page)->rightlink =3D data->newRightlink; + + PageSetLSN(page, lsn); + PageSetTLI(page, ThisTimeLineID); + MarkBufferDirty(buffer); + } + UnlockReleaseBuffer(buffer); + } + + UnlockReleaseBuffer(metabuffer); +} + +static void +ginRedoInsertListPage(XLogRecPtr lsn, XLogRecord *record) +{ + ginxlogInsertListPage *data =3D (ginxlogInsertListPage*) XLogRecGetData(r= ecord); + Buffer buffer; + Page page; + OffsetNumber l, off =3D FirstOffsetNumber; + int i, tupsize; + IndexTuple tuples =3D (IndexTuple) (XLogRecGetData(record) + sizeof(= ginxlogInsertListPage)); + + if (record->xl_info & XLR_BKP_BLOCK_1) + return; + + buffer =3D XLogReadBuffer(data->node, data->blkno, true); + page =3D BufferGetPage(buffer); + + GinInitBuffer(buffer, GIN_LIST); + GinPageGetOpaque(page)->rightlink =3D data->rightlink; + if ( data->rightlink =3D=3D InvalidBlockNumber ) + GinPageSetFullRow(page); + + for(i=3D0; intuples; i++) + { + tupsize =3D IndexTupleSize(tuples); + + l =3D PageAddItem(page, (Item)tuples, tupsize, off, false, false); + + if (l =3D=3D InvalidOffsetNumber) + elog(ERROR, "failed to add item to index page"); + + tuples =3D (IndexTuple)( ((char*)tuples) + tupsize ); + } + + PageSetLSN(page, lsn); + PageSetTLI(page, ThisTimeLineID); + MarkBufferDirty(buffer); + + UnlockReleaseBuffer(buffer); +} + +static void +ginRedoDeleteListPages(XLogRecPtr lsn, XLogRecord *record) +{ + ginxlogDeleteListPages *data =3D (ginxlogDeleteListPages*) XLogRecGetData= (record); + Buffer metabuffer; + Page metapage; + int i; + + metabuffer =3D XLogReadBuffer(data->node, GIN_METAPAGE_BLKNO, false); + metapage =3D BufferGetPage(metabuffer); + + if (!XLByteLE(lsn, PageGetLSN(metapage))) + { + memcpy( GinPageGetMeta(metapage), &data->metadata, sizeof(GinMetaPageDat= a)); + PageSetLSN(metapage, lsn); + PageSetTLI(metapage, ThisTimeLineID); + MarkBufferDirty(metabuffer); + } + + for(i=3D0; indeleted; i++) + { + Buffer buffer =3D XLogReadBuffer(data->node,data->toDelete[i],false); + Page page =3D BufferGetPage(buffer); + + if ( !XLByteLE(lsn, PageGetLSN(page))) + { + GinPageGetOpaque(page)->flags =3D GIN_DELETED; + + PageSetLSN(page, lsn); + PageSetTLI(page, ThisTimeLineID); + MarkBufferDirty(buffer); + } + + UnlockReleaseBuffer(buffer); + } + UnlockReleaseBuffer(metabuffer); +} + void gin_redo(XLogRecPtr lsn, XLogRecord *record) { @@ -459,6 +624,15 @@ gin_redo(XLogRecPtr lsn, XLogRecord *record) case XLOG_GIN_DELETE_PAGE: ginRedoDeletePage(lsn, record); break; + case XLOG_GIN_UPDATE_META_PAGE: + ginRedoUpdateMetapage(lsn, record); + break; + case XLOG_GIN_INSERT_LISTPAGE: + ginRedoInsertListPage(lsn, record); + break; + case XLOG_GIN_DELETE_LISTPAGE:=20 + ginRedoDeleteListPages(lsn, record); + break; default: elog(PANIC, "gin_redo: unknown op code %u", info); } @@ -514,6 +688,18 @@ gin_desc(StringInfo buf, uint8 xl_info, char *rec) appendStringInfo(buf, "Delete page, "); desc_node(buf, ((ginxlogDeletePage *) rec)->node, ((ginxlogDeletePage *= ) rec)->blkno); break; + case XLOG_GIN_UPDATE_META_PAGE: + appendStringInfo(buf, "Update metapage, "); + desc_node(buf, ((ginxlogUpdateMeta *) rec)->node, ((ginxlogUpdateMeta *= ) rec)->metadata.tail);=20 + break; + case XLOG_GIN_INSERT_LISTPAGE: + appendStringInfo(buf, "insert new list page, "); + desc_node(buf, ((ginxlogInsertListPage *) rec)->node, ((ginxlogInsertLi= stPage *) rec)->blkno);=20 + break; + case XLOG_GIN_DELETE_LISTPAGE: + appendStringInfo(buf, "Delete list page (%d), ", ((ginxlogDeleteListPag= es *) rec)->ndeleted); + desc_node(buf, ((ginxlogDeleteListPages *) rec)->node, ((ginxlogDeleteL= istPages *) rec)->metadata.head);=20 + break; default: elog(PANIC, "gin_desc: unknown op code %u", info); } diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/sys= tem_views.sql index 2fc6f05..4434ab4 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -193,6 +193,7 @@ CREATE VIEW pg_stat_all_tables AS pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,=20 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd, + pg_stat_get_fresh_inserted_tuples(C.oid) AS n_fresh_tup,=20 pg_stat_get_live_tuples(C.oid) AS n_live_tup,=20 pg_stat_get_dead_tuples(C.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(C.oid) as last_vacuum, diff --git a/src/backend/nodes/tidbitmap.c b/src/backend/nodes/tidbitmap.c index ffc882f..0864a04 100644 --- a/src/backend/nodes/tidbitmap.c +++ b/src/backend/nodes/tidbitmap.c @@ -306,6 +306,47 @@ tbm_add_tuples(TIDBitmap *tbm, const ItemPointer tids,= int ntids, } =20 /* + * tbm_check_tuple - Check presence of tuple's ID in a TIDBitmap + */ +bool +tbm_check_tuple(TIDBitmap *tbm, const ItemPointer tid) { + BlockNumber blk =3D ItemPointerGetBlockNumber(tid); + OffsetNumber off =3D ItemPointerGetOffsetNumber(tid); + PagetableEntry *page; + int wordnum, + bitnum; + + /* safety check to ensure we don't overrun bit array bounds */ + if (off < 1 || off > MAX_TUPLES_PER_PAGE) + elog(ERROR, "tuple offset out of range: %u", off); + + if (tbm_page_is_lossy(tbm, blk)) + return true; /* whole page is already marked */ + + page =3D tbm_get_pageentry(tbm, blk); + if (page->ischunk) + { + wordnum =3D bitnum =3D 0; + } + else + { + wordnum =3D WORDNUM(off - 1); + bitnum =3D BITNUM(off - 1); + } + + return ( page->words[wordnum] & ((bitmapword) 1 << bitnum) ) ? true : fal= se;=20 +} + +/* + * tbm_has_lossy - returns true if there is at least one lossy page + */ +bool +tbm_has_lossy(TIDBitmap *tbm) +{ + return (tbm->nchunks>0); +} + +/* * tbm_union - set union * * a is modified in-place, b is not changed diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/a= utovacuum.c index 2c68779..324ae44 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -2477,6 +2477,58 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_m= ap) } =20 /* + * relation_has_pending_indexes + * + * Returns true if relation has indexes with delayed insertion. + * Currently, only GIN has that possiblity + */ + +static bool +relation_has_pending_indexes(Oid relid, Form_pg_class classForm) +{ + Relation rel; + List *indexoidlist; + ListCell *indexoidscan; + bool has =3D false; + + /* only ordinary cataloged heap can contains such indexes */ + if ( classForm->relkind !=3D RELKIND_RELATION ) + return false; + + /* has not indexes at all */ + if ( classForm->relhasindex =3D=3D false ) + return false; + + rel =3D RelationIdGetRelation(relid); + + indexoidlist =3D RelationGetIndexList(rel); + + foreach(indexoidscan, indexoidlist) + { + Oid indexoid =3D lfirst_oid(indexoidscan); + Relation irel =3D RelationIdGetRelation(indexoid); + + /* + * Currently, only GIN=20 + */ + if ( irel->rd_rel->relam =3D=3D GIN_AM_OID ) + { + RelationClose(irel); + has =3D true; + break; + } + + RelationClose(irel); + } + + list_free(indexoidlist); + + RelationClose(rel); + + return has; +} + +/* * relation_needs_vacanalyze * * Check whether a relation needs to be vacuumed or analyzed; return each = into @@ -2533,7 +2585,8 @@ relation_needs_vacanalyze(Oid relid, =20 /* number of vacuum (resp. analyze) tuples at this time */ float4 vactuples, - anltuples; + anltuples, + instuples; =20 /* freeze parameters */ int freeze_max_age; @@ -2598,6 +2651,7 @@ relation_needs_vacanalyze(Oid relid, vactuples =3D tabentry->n_dead_tuples; anltuples =3D tabentry->n_live_tuples + tabentry->n_dead_tuples - tabentry->last_anl_tuples; + instuples =3D tabentry->n_inserted_tuples; =20 vacthresh =3D (float4) vac_base_thresh + vac_scale_factor * reltuples; anlthresh =3D (float4) anl_base_thresh + anl_scale_factor * reltuples; @@ -2611,8 +2665,13 @@ relation_needs_vacanalyze(Oid relid, NameStr(classForm->relname), vactuples, vacthresh, anltuples, anlthresh); =20 - /* Determine if this table needs vacuum or analyze. */ - *dovacuum =3D force_vacuum || (vactuples > vacthresh); + /*=20 + * Determine if this table needs vacuum or analyze.=20 + * Use vac_base_thresh as a theshhold for instuples because + * search time of GIN's pending pages is linear by its number.=20 + */ + *dovacuum =3D force_vacuum || (vactuples > vacthresh) ||=20 + (relation_has_pending_indexes(relid, classForm) && instuples > vac_bas= e_thresh); *doanalyze =3D (anltuples > anlthresh); } else diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgsta= t.c index 5ae0ec1..24573ff 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -3537,6 +3537,9 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len) tabentry->tuples_updated =3D tabmsg[i].t_counts.t_tuples_updated; tabentry->tuples_deleted =3D tabmsg[i].t_counts.t_tuples_deleted; tabentry->tuples_hot_updated =3D tabmsg[i].t_counts.t_tuples_hot_update= d; + tabentry->n_inserted_tuples =3D tabmsg[i].t_counts.t_tuples_inserted +=20 + tabmsg[i].t_counts.t_tuples_updated - + tabmsg[i].t_counts.t_tuples_hot_updated; tabentry->n_live_tuples =3D tabmsg[i].t_counts.t_new_live_tuples; tabentry->n_dead_tuples =3D tabmsg[i].t_counts.t_new_dead_tuples; tabentry->blocks_fetched =3D tabmsg[i].t_counts.t_blocks_fetched; @@ -3560,6 +3563,9 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len) tabentry->tuples_updated +=3D tabmsg[i].t_counts.t_tuples_updated; tabentry->tuples_deleted +=3D tabmsg[i].t_counts.t_tuples_deleted; tabentry->tuples_hot_updated +=3D tabmsg[i].t_counts.t_tuples_hot_updat= ed; + tabentry->n_inserted_tuples +=3D tabmsg[i].t_counts.t_tuples_inserted += =20 + tabmsg[i].t_counts.t_tuples_updated - + tabmsg[i].t_counts.t_tuples_hot_updated; tabentry->n_live_tuples +=3D tabmsg[i].t_counts.t_new_live_tuples; tabentry->n_dead_tuples +=3D tabmsg[i].t_counts.t_new_dead_tuples; tabentry->blocks_fetched +=3D tabmsg[i].t_counts.t_blocks_fetched; @@ -3570,6 +3576,8 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len) tabentry->n_live_tuples =3D Max(tabentry->n_live_tuples, 0); /* Likewise for n_dead_tuples */ tabentry->n_dead_tuples =3D Max(tabentry->n_dead_tuples, 0); + /* Likewise for n_inserted_tuples */ + tabentry->n_inserted_tuples =3D Max(tabentry->n_inserted_tuples, 0); =20 /* * Add per-table stats to the per-database entry, too. @@ -3770,6 +3778,7 @@ pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len) tabentry->n_live_tuples =3D msg->m_tuples; /* Resetting dead_tuples to 0 is an approximation ... */ tabentry->n_dead_tuples =3D 0; + tabentry->n_inserted_tuples =3D 0; if (msg->m_analyze) { if (msg->m_scanned_all) diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pg= statfuncs.c index 77c2baa..381de6f 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -31,6 +31,7 @@ extern Datum pg_stat_get_tuples_updated(PG_FUNCTION_ARGS)= ; extern Datum pg_stat_get_tuples_deleted(PG_FUNCTION_ARGS); extern Datum pg_stat_get_tuples_hot_updated(PG_FUNCTION_ARGS); extern Datum pg_stat_get_live_tuples(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_fresh_inserted_tuples(PG_FUNCTION_ARGS); extern Datum pg_stat_get_dead_tuples(PG_FUNCTION_ARGS); extern Datum pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS); extern Datum pg_stat_get_blocks_hit(PG_FUNCTION_ARGS); @@ -209,6 +210,20 @@ pg_stat_get_live_tuples(PG_FUNCTION_ARGS) PG_RETURN_INT64(result); } =20 +Datum +pg_stat_get_fresh_inserted_tuples(PG_FUNCTION_ARGS) +{ + Oid relid =3D PG_GETARG_OID(0); + int64 result; + PgStat_StatTabEntry *tabentry; + + if ((tabentry =3D pgstat_fetch_stat_tabentry(relid)) =3D=3D NULL) + result =3D 0; + else + result =3D (int64) (tabentry->n_inserted_tuples); + + PG_RETURN_INT64(result); +} =20 Datum pg_stat_get_dead_tuples(PG_FUNCTION_ARGS) diff --git a/src/include/access/gin.h b/src/include/access/gin.h index 0fd2cbd..f514358 100644 --- a/src/include/access/gin.h +++ b/src/include/access/gin.h @@ -21,6 +21,7 @@ #include "storage/buf.h" #include "storage/off.h" #include "storage/relfilenode.h" +#include "utils/rel.h" =20 =20 /* @@ -52,11 +53,34 @@ typedef struct GinPageOpaqueData =20 typedef GinPageOpaqueData *GinPageOpaque; =20 -#define GIN_ROOT_BLKNO (0) +#define GIN_METAPAGE_BLKNO (0) +#define GIN_ROOT_BLKNO (1) =20 #define GIN_DATA (1 << 0) #define GIN_LEAF (1 << 1) #define GIN_DELETED (1 << 2) +#define GIN_META (1 << 3) +#define GIN_LIST (1 << 4) +#define GIN_LIST_FULLROW (1 << 5) /* makes sense only on GIN_LIST page = */ + +typedef struct GinMetaPageData +{ + /* + * Pointers to head and tail of list of GIN_LIST pages. These store + * fast-inserted entries that haven't yet been moved into the regular + * GIN structure. + */ + BlockNumber head; + BlockNumber tail; + + /* + * Free space in bytes in the list's tail page. + */ + uint32 tailFreeSize; +} GinMetaPageData; + +#define GinPageGetMeta(p) \ + ((GinMetaPageData *) PageGetContents(p)) =20 /* * Works on page @@ -68,6 +92,8 @@ typedef GinPageOpaqueData *GinPageOpaque; #define GinPageSetNonLeaf(page) ( GinPageGetOpaque(page)->flags &=3D ~G= IN_LEAF ) #define GinPageIsData(page) ( GinPageGetOpaque(page)->flags & GIN_DATA = ) #define GinPageSetData(page) ( GinPageGetOpaque(page)->flags |=3D GIN_DA= TA ) +#define GinPageHasFullRow(page) ( GinPageGetOpaque(page)->flags & GIN_L= IST_FULLROW ) +#define GinPageSetFullRow(page) ( GinPageGetOpaque(page)->flags |=3D GIN= _LIST_FULLROW ) =20 #define GinPageIsDeleted(page) ( GinPageGetOpaque(page)->flags & GIN_DELET= ED) #define GinPageSetDeleted(page) ( GinPageGetOpaque(page)->flags |=3D GI= N_DELETED) @@ -135,6 +161,20 @@ typedef struct - GinPageGetOpaque(page)->maxoff * GinSizeOfItem(page) \ - MAXALIGN(sizeof(GinPageOpaqueData))) =20 +/* + * storage type for GIN's options. Must be upward compatible with + * StdRdOptions, since we might call RelationGetFillFactor(). + */ +typedef struct GinOptions +{ + StdRdOptions std; /* standard options */ + bool useFastUpdate; /* use fast updates? */ +} GinOptions; + +#define GIN_DEFAULT_USE_FASTUPDATE true +#define GinGetUseFastUpdate(relation) \ + ((relation)->rd_options ? \ + ((GinOptions *) (relation)->rd_options)->useFastUpdate : GIN_DEFAULT_USE= _FASTUPDATE) =20 #define GIN_UNLOCK BUFFER_LOCK_UNLOCK #define GIN_SHARE BUFFER_LOCK_SHARE @@ -234,12 +274,49 @@ typedef struct ginxlogDeletePage BlockNumber rightLink; } ginxlogDeletePage; =20 + +#define XLOG_GIN_UPDATE_META_PAGE 0x60 + +typedef struct ginxlogUpdateMeta +{ + RelFileNode node; + GinMetaPageData metadata; + BlockNumber prevTail; + BlockNumber newRightlink; + int32 ntuples; /* if ntuples > 0 then metadata.tail was updated= with + that tuples else new sub list was inserted */ + /* follows array of inserted tuples */ +} ginxlogUpdateMeta; + +#define XLOG_GIN_INSERT_LISTPAGE 0x70 + +typedef struct ginxlogInsertListPage +{ + RelFileNode node; + BlockNumber blkno; + BlockNumber rightlink; + int32 ntuples; + /* follows array of inserted tuples */ +} ginxlogInsertListPage; + +#define XLOG_GIN_DELETE_LISTPAGE 0x80 + +#define NDELETE_AT_ONCE (16) +typedef struct ginxlogDeleteListPages +{ + RelFileNode node; + GinMetaPageData metadata; + int32 ndeleted; + BlockNumber toDelete[ NDELETE_AT_ONCE ]; +} ginxlogDeleteListPages; + /* ginutil.c */ extern Datum ginoptions(PG_FUNCTION_ARGS); extern void initGinState(GinState *state, Relation index); extern Buffer GinNewBuffer(Relation index); extern void GinInitBuffer(Buffer b, uint32 f); extern void GinInitPage(Page page, uint32 f, Size pageSize); +extern void GinInitMetabuffer(Buffer b); extern int compareEntries(GinState *ginstate, OffsetNumber attnum, Datum a= , Datum b); extern int compareAttEntries(GinState *ginstate, OffsetNumber attnum_a, Da= tum a,=20 OffsetNumber attnum_b, Datum b); @@ -252,6 +329,8 @@ extern OffsetNumber gintuple_get_attrnum(GinState *gins= tate, IndexTuple tuple); /* gininsert.c */ extern Datum ginbuild(PG_FUNCTION_ARGS); extern Datum gininsert(PG_FUNCTION_ARGS); +extern void ginEntryInsert(Relation index, GinState *ginstate, OffsetNumbe= r attnum, Datum value, + ItemPointerData *items, uint32 nitem, bool isBuild); =20 /* ginxlog.c */ extern void gin_redo(XLogRecPtr lsn, XLogRecord *record); @@ -425,6 +504,10 @@ typedef struct GinScanOpaqueData uint32 nkeys; bool isVoidRes; /* true if ginstate.extractQueryFn guarantees * that nothing will be found */ + bool isScanFastInsert; /* scan process in scanning fast update pages *= / + TIDBitmap *scanFastTuples; =20 + TBMIterateResult *scanFastResult; + OffsetNumber scanFastOffset; } GinScanOpaqueData; =20 typedef GinScanOpaqueData *GinScanOpaque; @@ -488,4 +571,23 @@ extern void ginInsertRecordBA(BuildAccumulator *accum, OffsetNumber attnum, Datum *entries, int32 nentry); extern ItemPointerData *ginGetEntry(BuildAccumulator *accum, OffsetNumber = *attnum, Datum *entry, uint32 *n); =20 +/* ginfast.c */ + +typedef struct GinTupleCollector { + IndexTuple *tuples; + uint32 ntuples; + uint32 lentuples; + uint32 sumsize; +} GinTupleCollector;=20 + +extern void ginHeapTupleFastInsert(Relation index, GinTupleCollector *coll= ector); =20 +extern uint32 ginHeapTupleFastCollect(Relation index, GinState *ginstate, + GinTupleCollector *collector, + OffsetNumber attnum, Datum value, ItemPointer item); + +#define GinListPageSize \ + ( BLCKSZ - SizeOfPageHeaderData - MAXALIGN(sizeof(GinPageOpaqueData)) = ) + +extern Datum gininsertcleanup(PG_FUNCTION_ARGS); + #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index d405d82..9165f08 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2928,6 +2928,8 @@ DATA(insert OID =3D 1933 ( pg_stat_get_tuples_delete= d PGNSP PGUID 12 1 0 0 f f f DESCR("statistics: number of tuples deleted"); DATA(insert OID =3D 1972 ( pg_stat_get_tuples_hot_updated PGNSP PGUID 12 = 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_tuple= s_hot_updated _null_ _null_ _null_ )); DESCR("statistics: number of tuples hot updated"); +DATA(insert OID =3D 2316 ( pg_stat_get_fresh_inserted_tuples PGNSP PGUID = 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_fr= esh_inserted_tuples _null_ _null_ _null_ )); +DESCR("statistics: number of inserted tuples since last vacuum"); DATA(insert OID =3D 2878 ( pg_stat_get_live_tuples PGNSP PGUID 12 1 0 0 f= f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_live_tuples = _null_ _null_ _null_ )); DESCR("statistics: number of live tuples"); DATA(insert OID =3D 2879 ( pg_stat_get_dead_tuples PGNSP PGUID 12 1 0 0 f= f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_dead_tuples = _null_ _null_ _null_ )); diff --git a/src/include/nodes/tidbitmap.h b/src/include/nodes/tidbitmap.h index 56d6a0d..c8dbeea 100644 --- a/src/include/nodes/tidbitmap.h +++ b/src/include/nodes/tidbitmap.h @@ -49,6 +49,8 @@ extern void tbm_free(TIDBitmap *tbm); extern void tbm_add_tuples(TIDBitmap *tbm, const ItemPointer tids, int ntids, bool recheck); +extern bool tbm_check_tuple(TIDBitmap *tbm, const ItemPointer tid); +extern bool tbm_has_lossy(TIDBitmap *tbm); =20 extern void tbm_union(TIDBitmap *a, const TIDBitmap *b); extern void tbm_intersect(TIDBitmap *a, const TIDBitmap *b); diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 4a1e274..79754dc 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -476,6 +476,8 @@ typedef struct PgStat_StatTabEntry PgStat_Counter tuples_deleted; PgStat_Counter tuples_hot_updated; =20 + PgStat_Counter n_inserted_tuples; /* number of non-hot inserted tuples + * since last vacuum */=20 PgStat_Counter n_live_tuples; PgStat_Counter n_dead_tuples; PgStat_Counter last_anl_tuples; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expecte= d/rules.out index 977b17c..c037696 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1291,14 +1291,14 @@ SELECT viewname, definition FROM pg_views WHERE sch= emaname <> 'information_schem pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid= .oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper = AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS = passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, pg_authid.rolconfig= AS useconfig FROM pg_authid WHERE pg_authid.rolcanlogin; pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesys= id, u.rolname AS usename, s.current_query, s.waiting, s.xact_start, s.query= _start, s.backend_start, s.client_addr, s.client_port FROM pg_database d, p= g_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, current_quer= y, waiting, xact_start, query_start, backend_start, client_addr, client_por= t), pg_authid u WHERE ((s.datid =3D d.oid) AND (s.usesysid =3D u.oid)); pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.= nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_nu= mscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_re= ad, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c J= OIN pg_index x ON ((c.oid =3D x.indrelid))) JOIN pg_class i ON ((i.oid =3D = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid =3D c.relnamespace))) W= HERE (c.relkind =3D ANY (ARRAY['r'::"char", 't'::"char"])); - pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname= , c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_re= turned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::b= igint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint= + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_= inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_up= d, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_u= pdated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tu= p, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_ti= me(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_a= utovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_ge= t_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_class c LEFT J= OIN pg_index i ON ((c.oid =3D i.indrelid))) LEFT JOIN pg_namespace n ON ((n= .oid =3D c.relnamespace))) WHERE (c.relkind =3D ANY (ARRAY['r'::"char", 't'= ::"char"])) GROUP BY c.oid, n.nspname, c.relname; + pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname= , c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_re= turned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::b= igint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint= + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_= inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_up= d, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_u= pdated(c.oid) AS n_tup_hot_upd, pg_stat_get_fresh_inserted_tuples(c.oid) AS= n_fresh_tup, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dea= d_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_= vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat= _get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze= _time(c.oid) AS last_autoanalyze FROM ((pg_class c LEFT JOIN pg_index i ON = ((c.oid =3D i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid =3D c.relname= space))) WHERE (c.relkind =3D ANY (ARRAY['r'::"char", 't'::"char"])) GROUP = BY c.oid, n.nspname, c.relname; pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints(= ) AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS che= ckpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_che= ckpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat= _get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_writt= en_backend() AS buffers_backend, pg_stat_get_buf_alloc() AS buffers_alloc; pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_= db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS = xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat= _get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_r= ead, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_re= turned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_= fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get= _db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.o= id) AS tup_deleted FROM pg_database d; pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_= indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.rel= name, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_st= at_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat= _all_indexes WHERE ((pg_stat_all_indexes.schemaname =3D ANY (ARRAY['pg_cata= log'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemanam= e ~ '^pg_toast'::text)); - pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_t= ables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, = pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_t= ables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup= _upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_st= at_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables= .last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_a= nalyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE (= (pg_stat_all_tables.schemaname =3D ANY (ARRAY['pg_catalog'::name, 'informat= ion_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)= ); + pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_t= ables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, = pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_t= ables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup= _upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_st= at_all_tables.n_fresh_tup, pg_stat_all_tables.n_live_tup, pg_stat_all_table= s.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autov= acuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze= FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname =3D ANY (ARR= AY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables= .schemaname ~ '^pg_toast'::text)); pg_stat_user_functions | SELECT p.oid AS funcid, n.nspname AS schemanam= e, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, (pg_s= tat_get_function_time(p.oid) / 1000) AS total_time, (pg_stat_get_function_s= elf_time(p.oid) / 1000) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace= n ON ((n.oid =3D p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg= _stat_get_function_calls(p.oid) IS NOT NULL)); pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_= indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.rel= name, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_st= at_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat= _all_indexes WHERE ((pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catal= og'::name, 'information_schema'::name])) AND (pg_stat_all_indexes.schemanam= e !~ '^pg_toast'::text)); - pg_stat_user_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_t= ables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, = pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_t= ables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup= _upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_st= at_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables= .last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_a= nalyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE (= (pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'informati= on_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text= )); + pg_stat_user_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_t= ables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, = pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_t= ables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup= _upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_st= at_all_tables.n_fresh_tup, pg_stat_all_tables.n_live_tup, pg_stat_all_table= s.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autov= acuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze= FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRA= Y['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables= .schemaname !~ '^pg_toast'::text)); pg_statio_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.= nspname AS schemaname, c.relname, i.relname AS indexrelname, (pg_stat_get_b= locks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, pg_= stat_get_blocks_hit(i.oid) AS idx_blks_hit FROM (((pg_class c JOIN pg_index= x ON ((c.oid =3D x.indrelid))) JOIN pg_class i ON ((i.oid =3D x.indexrelid= ))) LEFT JOIN pg_namespace n ON ((n.oid =3D c.relnamespace))) WHERE (c.relk= ind =3D ANY (ARRAY['r'::"char", 't'::"char"])); pg_statio_all_sequences | SELECT c.oid AS relid, n.nspname AS schemaname= , c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.= oid)) AS blks_read, pg_stat_get_blocks_hit(c.oid) AS blks_hit FROM (pg_clas= s c LEFT JOIN pg_namespace n ON ((n.oid =3D c.relnamespace))) WHERE (c.relk= ind =3D 'S'::"char"); pg_statio_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname= , c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.= oid)) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, (s= um((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.ind= exrelid))))::bigint AS idx_blks_read, (sum(pg_stat_get_blocks_hit(i.indexre= lid)))::bigint AS idx_blks_hit, (pg_stat_get_blocks_fetched(t.oid) - pg_sta= t_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) = AS toast_blks_hit, (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_= hit(x.oid)) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_h= it FROM ((((pg_class c LEFT JOIN pg_index i ON ((c.oid =3D i.indrelid))) LE= FT JOIN pg_class t ON ((c.reltoastrelid =3D t.oid))) LEFT JOIN pg_class x O= N ((t.reltoastidxid =3D x.oid))) LEFT JOIN pg_namespace n ON ((n.oid =3D c.= relnamespace))) WHERE (c.relkind =3D ANY (ARRAY['r'::"char", 't'::"char"]))= GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid; --=-3XsaW2HftjHcEtth4W6T--