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

[Martin Davis]

As others said, indexing time is proportional to table size (actually more
like O( N logN )).   So if the number of rows is reduced the index build
time will decrease.

In a recent blog post Paul listed some ideas about how index build
performance could be improved via parallelization [1].  But that will
require some changes in Postgres.  (Although give the recent flurry of
parallelization enhancements, maybe it won't be long now).

It would be nifty if GIST trees could be packed/bulk-loaded using one of
the Rtree packing approaches (STR-tree or Hilbert) [2].  That should be
faster than one-by-one insertion.  Although might not be as amenable to
parallelization.

[1] http://blog.cleverelephant.ca/2018/10/postgis-sprint-2.html
[2] https://en.wikipedia.org/wiki/R-tree


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!
>
> Thank you,
> Wenbo Tao
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190114/ed9dee45/attachment.html>