From: | Andrey Borodin <x4mmm(at)yandex-team(dot)ru> |
---|---|
To: | Darafei Komяpa Praliaskouski <me(at)komzpa(dot)net>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Yet another fast GiST build |
Date: | 2019-08-26 07:59:11 |
Message-ID: | 1A36620E-CAD8-4267-9067-FB31385E7C0D@yandex-team.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi!
In many cases GiST index can be build fast using z-order sorting.
I've looked into proof of concept by Nikita Glukhov [0] and it looks very interesting.
So, I've implemented yet another version of B-tree-like GiST build.
It's main use case and benefits can be summarized with small example:
postgres=# create table x as select point (random(),random()) from generate_series(1,3000000,1);
SELECT 3000000
Time: 5061,967 ms (00:05,062)
postgres=# create index ON x using gist (point ) with (fast_build_sort_function=gist_point_sortsupport);
CREATE INDEX
Time: 6140,227 ms (00:06,140)
postgres=# create index ON x using gist (point );
CREATE INDEX
Time: 32061,200 ms (00:32,061)
As you can see, Z-order build is on order of magnitude faster. Select performance is roughly the same. Also, index is significantly smaller.
Nikita's PoC is faster because it uses parallel build, but it intervenes into B-tree code a lot (for reuse). This patchset is GiST-isolated.
My biggest concern is that passing function to relation option seems a bit hacky. You can pass there any function matching sort support signature.
Embedding this function into opclass makes no sense: it does not affect scan anyhow.
In current version, docs and tests are not implemented. I want to discuss overall design. Do we really want yet another GiST build, if it is 3-10 times faster?
Thanks!
Best regards, Andrey Borodin.
[0] https://github.com/postgres/postgres/compare/master...glukhovn:gist_btree_build
Attachment | Content-Type | Size |
---|---|---|
0001-function-relopt-for-gist-build.patch | application/octet-stream | 4.9 KB |
0003-Implement-GiST-build-using-sort-support.patch | application/octet-stream | 15.6 KB |
0002-Add-sort-support-for-point-gist_point_sortsupport.patch | application/octet-stream | 2.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Nancarrow | 2019-08-26 08:01:04 | Re: Procedure support improvements |
Previous Message | Masahiko Sawada | 2019-08-26 07:51:57 | Re: Can't we give better table bloat stats easily? |