<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<p>Wenbo,</p>
<p>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:<br>
</p>
<p>1. You may also look in to table partitioning to physically
breakup the large table.</p>
<p>2. In addition, depend on how your data is used and whether or
not the data is static, vector-tiling/cacheing the geometry on
disk (out-side of database) may help.<br>
</p>
<p> Bo</p>
<p><br>
</p>
<div class="moz-cite-prefix">On 1/12/19 9:28 AM, Wenbo Tao wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CAJE-R6p00bsttwBDntZfetf+44yXuVML62iUNTenXwyFs7zeow@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">Hello,
<div><br>
</div>
<div> 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. </div>
<div><br>
</div>
<div> 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. </div>
<div><br>
</div>
<div> 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!</div>
<div><br>
</div>
<div>Thank you,</div>
<div>Wenbo Tao</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<div class="moz-signature"><br>
</div>
</body>
</html>