[postgis-users] Slow construction of GiST index, but better with smaller # of big rows

[Martin Davis]

Possibly relevant - a presentation on how BRIN indexes can provide better
performance and reduce storage for very large point datasets:

https://www.postgresql-sessions.org/_media/8/gbroccolo_jrouhaud_pgsession_brin4postgis.pdf

On Sat, Jan 12, 2019 at 8:29 AM Wenbo Tao <taowenbo1993 at gmail.com> wrote:

> Hello,
>
>     I was trying to build a GiST index on a geometry column in a table
> with 1 billion rows. It took an entire week to finish.
>
>     Then I reduced the number of rows by grouping closer objects into one
> clump (using some clustering algorithm), and then compressed the clump as
> one row (the geometry column becomes the bounding box of all objects in
> that clump). The construction then went way faster -- down to 12 hours. I
> did this because the query I need to answer is finding all objects whose
> bbox intersects with a given rectangle. I can now query all clumps whose
> bbox intersects with the rectangle.
>
>    So essentially, the index construction is slow for too many rows, but
> much faster for a smaller # of bigger rows. Any intuition why this is the
> case would be greatly appreciated!
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190117/af203dbc/attachment.html>