Fillfactor for GIN indexes
От | Michael Paquier |
---|---|
Тема | Fillfactor for GIN indexes |
Дата | |
Msg-id | CAB7nPqR36boo_qNnkGzq53vLwET5_E9bXJsusojGqN2TopXx9w@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Fillfactor for GIN indexes
Re: Fillfactor for GIN indexes |
Список | 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
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
Вложения
В списке pgsql-hackers по дате отправления: