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

On 2019-01-12 8:28 a.m., Wenbo Tao wrote:  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! GiST indexes work on bounding boxes only, so the portion of the row tha

Re: [postgis-users] topology face geometry vs mbr

Thanks, Tumasgiu. On 1/11/19 1:29 AM, Tumasgiu Rossini wrote: Hi, the face table store indeed the mbr of the faces created by your topology. To access it, you can use the st_getfacegeometry function. https://postgis.net/docs/ST_GetFaceGeometry.html Le jeu. 10 janv. 2019 à 22:01, Bo Guo

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

Hi Wembo, Il giorno sab 12 gen 2019 alle ore 16:29 Wenbo Tao ha scritto: > 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 > cl

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

Wenbo, The law of physics is in play here.  I think your approach is creative and valid.  I wish Postgis offered grid-base spatial index which grouped geometry BBOXs in grids with user defined levels /sizes.  A couple of other thoughts: 1. You may also look in to table partitioning to physic