Fillfactor for GIN indexes - Mailing list pgsql-hackers

From Michael Paquier
Subject Fillfactor for GIN indexes
Date
Msg-id CAB7nPqR36boo_qNnkGzq53vLwET5_E9bXJsusojGqN2TopXx9w@mail.gmail.com
Whole thread Raw
Responses Re: Fillfactor for GIN indexes
Re: Fillfactor for GIN indexes
List pgsql-hackers
Hi all,

Please find attached a simple patch adding fillfactor as storage parameter for GIN indexes. The default value is the same as the one currently aka 100 to have the pages completely packed when a GIN index is created.

Note that to have this feature correctly working, the fix I sent yesterday to set up isBuild for the entry insertion is needed (patch attached as well here to facilitate the review):
http://www.postgresql.org/message-id/CAB7nPqSc4VQ9mHKqm_YvAfcTEhO-iUY8SKbXYdnMGnAi1XnPaw@mail.gmail.com

Here are the results of some tests with a simple pg_trgm index on the English translation of "Les Miserables":
CREATE EXTENSION pg_trgm;
CREATE TABLE les_miserables (num serial, line text);
COPY les_miserables (line) FROM '/to/path/pg135.txt';
CREATE INDEX les_miserables_100 ON les_miserables USING gin (line gin_trgm_ops);
CREATE INDEX les_miserables_40 ON les_miserables USING gin (line gin_trgm_ops) with (fillfactor = 40);
CREATE INDEX les_miserables_20 ON les_miserables USING gin (line gin_trgm_ops) with (fillfactor = 20);
CREATE INDEX les_miserables_80 ON les_miserables USING gin (line gin_trgm_ops) with (fillfactor = 80);
CREATE INDEX les_miserables_10 ON les_miserables USING gin (line gin_trgm_ops) with (fillfactor = 10);
SELECT relname, pg_size_pretty(pg_relation_size(oid)), reloptions FROM pg_class where relname like 'les_miserables_%';
        relname         | pg_size_pretty |   reloptions  
------------------------+----------------+-----------------
 les_miserables_100     | 8256 kB        | null
 les_miserables_20      | 14 MB          | {fillfactor=20}
 les_miserables_40      | 11 MB          | {fillfactor=40}
 les_miserables_80      | 8712 kB        | {fillfactor=80}
 les_miserables_num_seq | 8192 bytes     | null
(5 rows)

I am adding that to the commit fest of December.
Regards,
--
Michael
Attachment

pgsql-hackers by date:

Previous
From: Ian Barwick
Date:
Subject: Comment header for src/test/regress/regress.c
Next
From: Anssi Kääriäinen
Date:
Subject: Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}