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

[Wenbo Tao]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190112/c922c369/attachment.html>