Thread: pgsql: Add gin_clean_pending_list function to clean up GIN pending list
Add gin_clean_pending_list function to clean up GIN pending list This function cleans up the pending list of the GIN index by moving entries in it to the main GIN data structure in bulk. It returns the number of pages cleaned up from the pending list. This function is useful, for example, when the pending list needs to be cleaned up *quickly* to improve the performance of the search using GIN index. VACUUM can do the same thing, too, but it may take days to run on a large table. Jeff Janes, reviewed by Julien Rouhaud, Jaime Casanova, Alvaro Herrera and me. Discussion: CAMkU=1x8zFkpfnozXyt40zmR3Ub_kHu58LtRmwHUKRgQss7=iQ@mail.gmail.com Branch ------ master Details ------- http://git.postgresql.org/pg/commitdiff/7f46eaf035440564e7dae3b03916735b92c83248 Modified Files -------------- doc/src/sgml/func.sgml | 26 ++++++++++++++++++ doc/src/sgml/gin.sgml | 4 ++- doc/src/sgml/ref/create_index.sgml | 4 +-- src/backend/access/gin/ginfast.c | 52 ++++++++++++++++++++++++++++++++++++ src/include/access/gin_private.h | 3 +++ src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.h | 2 ++ src/test/regress/expected/gin.out | 13 +++++++++ src/test/regress/sql/gin.sql | 6 +++++ 9 files changed, 108 insertions(+), 4 deletions(-)
Fujii Masao <fujii@postgresql.org> writes: > Add gin_clean_pending_list function to clean up GIN pending list The regression test case added by this commit is unstable, as per http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=spoonbill&dt=2016-02-06%2000%3A00%3A06 I imagine what happened there is that autovacuum came along and cleared the pending-list before the test query could. regards, tom lane
On Sat, Feb 6, 2016 at 1:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Fujii Masao <fujii@postgresql.org> writes: >> Add gin_clean_pending_list function to clean up GIN pending list > > The regression test case added by this commit is unstable, as per > http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=spoonbill&dt=2016-02-06%2000%3A00%3A06 > > I imagine what happened there is that autovacuum came along and cleared > the pending-list before the test query could. Thanks for the report! Yes, that's possible. I'm thinking to disable autovacuum for the index which pg_clean_pending_list() is working on by setting autovacuum_enabled reloption when creating the table. Patch attached. This patch also sets gin_pending_list_limit reloption to 4MB, just to be safe, so that "make installcheck" will not fail even when gin_pending_list_limit GUC is very small. Barring objection, I'll push this patch. Regards, -- Fujii Masao
Attachment
On Mon, Feb 8, 2016 at 2:19 AM, Fujii Masao <masao.fujii@gmail.com> wrote: > On Sat, Feb 6, 2016 at 1:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Fujii Masao <fujii@postgresql.org> writes: >>> Add gin_clean_pending_list function to clean up GIN pending list >> >> The regression test case added by this commit is unstable, as per >> http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=spoonbill&dt=2016-02-06%2000%3A00%3A06 >> >> I imagine what happened there is that autovacuum came along and cleared >> the pending-list before the test query could. > > Thanks for the report! Yes, that's possible. I'm thinking to disable > autovacuum for the index which pg_clean_pending_list() is working on > by setting autovacuum_enabled reloption when creating the table. > Patch attached. This patch also sets gin_pending_list_limit reloption > to 4MB, just to be safe, so that "make installcheck" will not fail > even when gin_pending_list_limit GUC is very small. > > Barring objection, I'll push this patch. Okay, pushed. Regards, -- Fujii Masao