Re: [HACKERS] New gist vacuum.
Thank you, Jeff.I reworking patch now. All // warning will be deleted.About memory consumption new version will control size of stack and will operate with map of little size because i want delete old style vacuum(now if maintenance_work_mem less than needed to build info map we use old-style vacuum with logical order).
[HACKERS] New gist vacuum.
Hello. I am student from gsoc programm.My project is sequantial access in vacuum of gist. New vacuum has 2 big step:physical order scan pages and cleaning after 1 step. 1 scan - scan all pages and create information map(hashmap) and add information to rescan stack( stack of pages that needed to rescanning second step is work only with page(from rescan stack) where there is a changes. In new version of vacuum besides increased speed also there is a deleting of pages. Only leaf pages can be deleted. The process of deleteing pages is (1. delete link to page. 2. change rightlinks (if needed) 3. set deleted). I added 2 action in wal (when i set delete flag and when i change rightlinks). When i delete links to leaf pages from inner page i always save 1 link to leaf(avoiding situations with empty inner pages).I attach some speed benchmarks.i compare old and new version on my laptop(without ssd). the test: table "point_tbl" from regression database. i insert about 200 millions rows. after that i delete 33 million and run vacuum.size of index is about 18 gb.old version:INFO: vacuuming "public.point_tbl"INFO: scanned index "gpointind" to remove 11184520 row versionsDETAIL: CPU 84.70s/72.26u sec elapsed 27007.14 sec.INFO: "point_tbl": removed 11184520 row versions in 400715 pagesDETAIL: CPU 3.96s/3.10u sec elapsed 233.12 sec.INFO: scanned index "gpointind" to remove 11184523 row versionsDETAIL: CPU 87.10s/69.05u sec elapsed 26410.44 sec.INFO: "point_tbl": removed 11184523 row versions in 400715 pagesDETAIL: CPU 4.23s/3.36u sec elapsed 331.43 sec.INFO: scanned index "gpointind" to remove 11184523 row versionsDETAIL: CPU 87.65s/65.73u sec elapsed 26230.35 sec.INFO: "point_tbl": removed 11184523 row versions in 400715 pagesDETAIL: CPU 4.47s/3.41u sec elapsed 342.93 sec.INFO: scanned index "gpointind" to remove 866 row versionsDETAIL: CPU 79.97s/39.64u sec elapsed 23341.88 sec.INFO: "point_tbl": removed 866 row versions in 31 pagesDETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: index "gpointind" now contains 201326592 row versions in 2336441 pagesDETAIL: 33554432 index row versions were removed.0 index pages have been deleted, 0 are currently reusable. new vacuum is about INFO: vacuuming "public.point_tbl"INFO: scanned index "gpointind" to remove 11184520 row versionsDETAIL: CPU 13.00s/27.57u sec elapsed 1864.22 sec.INFO: "point_tbl": removed 11184520 row versions in 400715 pagesDETAIL: CPU 3.46s/2.86u sec elapsed 214.04 sec.INFO: scanned index "gpointind" to remove 11184523 row versionsDETAIL: CPU 14.17s/27.02u sec elapsed 2163.67 sec.INFO: "point_tbl": removed 11184523 row versions in 400715 pagesDETAIL: CPU 3.33s/2.99u sec elapsed 222.60 sec.INFO: scanned index "gpointind" to remove 11184523 row versionsDETAIL: CPU 11.84s/25.23u sec elapsed 1828.71 sec.INFO: "point_tbl": removed 11184523 row versions in 400715 pagesDETAIL: CPU 3.44s/2.81u sec elapsed 215.06 sec.INFO: scanned index "gpointind" to remove 866 row versionsDETAIL: CPU 5.62s/6.68u sec elapsed 176.67 sec.INFO: "point_tbl": removed 866 row versions in 31 pagesDETAIL: CPU 0.00s/0.00u sec elapsed 0.01 sec.INFO: index "gpointind" now contains 201326592 row versions in 2336360 pagesDETAIL: 33554432 index row versions were removed.150833 index pages have been deleted, 150833 are currently reusable.CPU 5.54s/2.08u sec elapsed 165.61 sec.INFO: "point_tbl": found 33554432 removable, 201326592 nonremovable row versions in 1202176 out of 1202176 pagesDETAIL: 0 dead row versions cannot be removed yet.There were 0 unused item pointers.Skipped 0 pages due to buffer pins.0 pages are entirely empty.CPU 73.50s/116.82u sec elapsed 8300.73 sec.INFO: analyzing "public.point_tbl"INFO: "point_tbl": scanned 100 of 1202176 pages, containing 16756 live rows and 0 dead rows; 100 rows in sample, 201326601 estimated total rowsVACUUM There is a big speed up + we can reuse some pages.Thanks.diff --git a/src/backend/access/gist/gist.c b/src/backend/access/gist/gist.c
index 0e49959..229d3f4 100644
--- a/src/backend/access/gist/gist.c
+++ b/src/backend/access/gist/gist.c
@@ -619,6 +619,12 @@ gistdoinsert(Relation r, IndexTuple itup, Size freespace, GISTSTATE *giststate)
GISTInsertStack *item;
OffsetNumber downlinkoffnum;
+ if(GistPageIsDeleted(stack->page)) {
+UnlockReleaseBuffer(stack->buffer);
+xlocked = false;
+state.stack = stack = stack->parent;
+continue;
+ }
downlinkoffnum = gistchoose(state.r, stack->page, itup, giststate);
iid = PageGetItemId(stack->page, downlinkoffnum);
idxtuple = (IndexTuple) PageGetItem(stack->page, iid);
diff --git a/src/backend/access/gist/gistbuild.c b/src/backend/access/gist/gistbuild.c
index ff888e2..c99ff7e 100644
--- a/src/backend/access/gist/gistbuild.c
+++ b/src/backend/access/gist/gistbuild.c
@@ -1128,11 +1128,6 @@ gistGetMaxLevel(Relation index)
* but will be added there the first time we visit them.
*/
-typedef struct
-{
- BlockNumber childblkno; /* hash key */
- BlockNumber parentblkno;
-}
Re: [HACKERS] gist vacuum seaq access
Heikki.I have idea. when i begining vacuum i must create structure like hash table. And the first action i fill this hash table.in code this look like:for( blkno = ..; all pages; blkno++) { if(! gistPageIsLeaf(blkno)) { for( all tuples in blkno ) { hash[ blkno that is referenced by the tuple] = blkno; } }}getting is parent of page is fast and simple( hash[page] ).But this solution is need 2 full reading of index. but Alexander says me that: this solution may be have a problem, because there is maintenance_work_mem.
[HACKERS] gist vacuum seaq access
After discussion of gist seaq access in vaccum there are 2 issue: Heikki says :Vacuum needs to memorize the current NSN when it begins1) how i may getting corect NSN. Also i must setting F_DELETED flag on empty page and to clean parent from link on deleted_pages2) how i may getting parent of page fast?? Thanks.
[HACKERS] gist vacuum gist access
hello. i recode vacuum for gist index.all tests is ok.also i test vacuum on table size 2 million rows. all is ok.on my machine old vaccum work about 9 second. this version work about 6-7 sec .review please. thanks.*** a/src/backend/access/gist/gistvacuum.c
--- b/src/backend/access/gist/gistvacuum.c
***
*** 101,134 gistvacuumcleanup(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(stats);
}
- typedef struct GistBDItem
- {
- GistNSN parentlsn;
- BlockNumber blkno;
- struct GistBDItem *next;
- } GistBDItem;
-
- static void
- pushStackIfSplited(Page page, GistBDItem *stack)
- {
- GISTPageOpaque opaque = GistPageGetOpaque(page);
-
- if (stack-blkno != GIST_ROOT_BLKNO !XLogRecPtrIsInvalid(stack-parentlsn)
- (GistFollowRight(page) || stack-parentlsn GistPageGetNSN(page))
- opaque-rightlink != InvalidBlockNumber /* sanity check */ )
- {
- /* split page detected, install right link to the stack */
-
- GistBDItem *ptr = (GistBDItem *) palloc(sizeof(GistBDItem));
-
- ptr-blkno = opaque-rightlink;
- ptr-parentlsn = stack-parentlsn;
- ptr-next = stack-next;
- stack-next = ptr;
- }
- }
-
-
/*
* Bulk deletion of all index entries pointing to a set of heap tuples and
* check invalid tuples left after upgrade.
--- 101,106
***
*** 145,283 gistbulkdelete(PG_FUNCTION_ARGS)
IndexBulkDeleteCallback callback = (IndexBulkDeleteCallback) PG_GETARG_POINTER(2);
void *callback_state = (void *) PG_GETARG_POINTER(3);
Relation rel = info-index;
! GistBDItem *stack,
! *ptr;
!
! /* first time through? */
if (stats == NULL)
stats = (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult));
- /* we'll re-count the tuples each time */
stats-estimated_count = false;
stats-num_index_tuples = 0;
! stack = (GistBDItem *) palloc0(sizeof(GistBDItem));
! stack-blkno = GIST_ROOT_BLKNO;
!
! while (stack)
! {
! Buffer buffer;
! Page page;
! OffsetNumber i,
! maxoff;
! IndexTuple idxtuple;
! ItemId iid;
!
! buffer = ReadBufferExtended(rel, MAIN_FORKNUM, stack-blkno,
! RBM_NORMAL, info-strategy);
! LockBuffer(buffer, GIST_SHARE);
! gistcheckpage(rel, buffer);
! page = (Page) BufferGetPage(buffer);
!
! if (GistPageIsLeaf(page))
{
! OffsetNumber todelete[MaxOffsetNumber];
! int ntodelete = 0;
!
! LockBuffer(buffer, GIST_UNLOCK);
! LockBuffer(buffer, GIST_EXCLUSIVE);
page = (Page) BufferGetPage(buffer);
- if (stack-blkno == GIST_ROOT_BLKNO !GistPageIsLeaf(page))
- {
- /* only the root can become non-leaf during relock */
- UnlockReleaseBuffer(buffer);
- /* one more check */
- continue;
- }
-
- /*
- * check for split proceeded after look at parent, we should check
- * it after relock
- */
- pushStackIfSplited(page, stack);
! /*
! * Remove deletable tuples from page
! */
!
! maxoff = PageGetMaxOffsetNumber(page);
!
! for (i = FirstOffsetNumber; i = maxoff; i = OffsetNumberNext(i))
{
! iid = PageGetItemId(page, i);
! idxtuple = (IndexTuple) PageGetItem(page, iid);
!
! if (callback((idxtuple-t_tid), callback_state))
{
! todelete[ntodelete] = i - ntodelete;
! ntodelete++;
! stats-tuples_removed += 1;
}
- else
- stats-num_index_tuples += 1;
- }
-
- if (ntodelete)
- {
- START_CRIT_SECTION();
-
- MarkBufferDirty(buffer);
-
- for (i = 0; i ntodelete; i++)
- PageIndexTupleDelete(page, todelete[i]);
- GistMarkTuplesDeleted(page);
! if (RelationNeedsWAL(rel))
{
! XLogRecPtr recptr;
! recptr = gistXLogUpdate(rel-rd_node, buffer,
! todelete, ntodelete,
! NULL, 0, InvalidBuffer);
! PageSetLSN(page, recptr);
! }
! else
! PageSetLSN(page, gistGetFakeLSN(rel));
!
! END_CRIT_SECTION();
! }
! }
! else
! {
! /* check for split proceeded after look at parent */
! pushStackIfSplited(page, stack);
!
! maxoff = PageGetMaxOffsetNumber(page);
! for (i = FirstOffsetNumber; i = maxoff; i = OffsetNumberNext(i))
! {
! iid = PageGetItemId(page, i);
! idxtuple = (IndexTuple) PageGetItem(page, iid);
! ptr = (GistBDItem *) palloc(sizeof(GistBDItem));
! ptr-blkno = ItemPointerGetBlockNumber((idxtuple-t_tid));
! ptr-parentlsn = PageGetLSN(page);
! ptr-next = stack-next;
! stack-next = ptr;
! if (GistTupleIsInvalid(idxtuple))
! ereport(LOG,
! (errmsg(index \%s\ contains an inner tuple marked as invalid,
! RelationGetRelationName(rel)),
! errdetail(This is caused by an incomplete page split at crash recovery before upgrading to PostgreSQL 9.1.),
! errhint(Please REINDEX it.)));
}
- }
-
- UnlockReleaseBuffer(buffer);
! ptr = stack-next;
! pfree(stack);
! stack = ptr;
!
! vacuum_delay_point();
}
PG_RETURN_POINTER(stats);
}
--- 117,208
[HACKERS] Sequential disk access during VACUUM for GiST/GIN
Hello.There is a task "Sequential disk access during VACUUM for GiST/GIN " in list GSOC14.Nobody is working on this task?Do I understand this task correctly?I must recode gistbulkdelete.GistBDItem *stack is must have items with sequential blkno as possible.I have a question:where are access to disk in this function? ReadBufferExtended?Thanks
[HACKERS] gsoc knn spgist
Helllo. I want to implement knn for spgist. I dont have question with knn, but have questions with implementation of interface. i modify pg_am.h (set amcanorderbyop to true in spgist index).Also i modify pg_amop.h(add DATA(insert ( 4015 600 600 15 o 517 4000 1970 )); ) explain SELECT * FROM quad_point_tbl ORDER BY p - '-2,50' Sort (cost=1219.31..1246.82 rows=11003 width=16) Sort Key: ((p - '(-2,50)'::point)) - Index Only Scan using sp_quad_ind on quad_point_tbl (cost=0.15..480.70 rows=11003 width=16)what am I doing wrong?In a gist index we have : regression=# explain SELECT * FROM point_tbl ORDER BY f1 - '-3,0'; QUERY PLAN Index Scan using gpointind on point_tbl (cost=0.13..8.27 rows=7 width=16) Order By: (f1 - '(-3,0)'::point) Planning time: 0.166 ms(3 rows) Are there documents about modification access methods? Thanks.Constantine Kuznetsov
[HACKERS] gsoc knn spgist
Hello. I submit a proposal. But Heikki Linnakangas write comments that i dont have a plan of implementation. My project is knn for spgist. Can I ask you a few questions? 1. I research a commit gist knn implementation. in gist implementation in role of queue is ised rtree(with distance comparator) , in spgist implementation this is List. Can i use rtree in spgist ? if i cant then i can use. Thanks. Constantine Kuznetsov
