[postgis-users] Parallel spatial indexing for GiST?

[Marco Boeringa]

Thanks Peter,

That certainly looks promising, and it is heartening to see real work 
already being done, although there appears to be no mention of Polygon 
type data being tested as well, but I don't know if geometry type is 
actually relevant to developing a faster GiST (spatial) index build. 
Probably not.

10x improvement would be great... love to see that land in PostgreSQL 13!

Marco

Op 16-9-2020 om 22:13 schreef Peter Geoghegan:
> On Wed, Sep 16, 2020 at 12:54 PM Marco Boeringa <marco at boeringa.demon.nl> wrote:
>> Appreciate your insights. Good to hear there appear to be opportunities
>> for improvements to GiST index build speed in the future, even if no
>> active work is being done right now. Yes, I do think a lot of people,
>> and an increasing number, could benefit from such work. I personally
>> would certainly applaud any improvements being made, as it is especially
>> clear that disk speed is not an issue in most of the processing
>> involved, and disk speed therefor unlikely to become limiting with any
>> improvements in index creation, meaning there is likely a good
>> opportunity for improving GiST index build speed.
> Actually, there is ongoing work to speed up GiST index builds by using
> Z-order + sorting:
>
> https://postgr.es/m/123F5F32-9A85-4D0B-9C7A-1686E6BBE15D@yandex-team.ru
>
> It reportedly can be as much as 10x faster. Building the index through
> sorting rather than using retail inserts is inherently much faster.
> And, we've put a lot of effort into speeding up B-Tree index
> builds/sorting over several releases, to the point where a B-Tree
> index build can be very I/O bound even on high end hardware. It seems
> possible that GiST could get much of the benefit of that work by
> adopting index builds to use Z-order.
>
> The tricky part may be getting that benefit without significantly
> impacting the final index structure. The idea of teaching GiST to
> build indexes in a way that's a lot closer to B-Tree seems very
> promising, though.
>