<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
Forgot to mention that we did run vacuum analyze without seeing much improvement.</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
Using PostgresSQL 10.3 and Postgis 2.4.4 on Windows 7.<br>
</div>
<hr tabindex="-1" style="width: 98%; display: inline-block;">
<div id="divRplyFwdMsg" dir="ltr"><font color="#000000" face="Calibri, sans-serif" style="font-size: 11pt;"><b>Fra:</b> pgpointcloud <pgpointcloud-bounces@lists.osgeo.org> på vegne av Lars <laasunde@hotmail.com><br>
<b>Sendt:</b> 2. mai 2018 14:01<br>
<b>Til:</b> pgpointcloud@lists.osgeo.org<br>
<b>Emne:</b> [pgpointcloud] PC_Intersects and indexing</font>
<div> </div>
</div>
<style style="display: none;" type="text/css">
<!--
p
{margin-top:0;
margin-bottom:0}
-->
</style>
<div dir="ltr">
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt; background-color: transparent;">
<p style="margin-top: 0px; margin-bottom: 0px;">Hello,</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">Used PDAL (1.7.1) to import xyz data into a PostgresSQL "pointcloud" table called "import" with chipper capacity set to 4000. The table has about 50,000 rows with more than 2GB of data.</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">The "import" table contains column id and pa which are of type integer and pcpatch respectively. The table is configured with btree indexing on the id column.</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">Running the following query takes 776,952 ms and returns two records.<br>
select id from import where PC_Intersects(pa, st_geomfromtext('POLYGON ((6.9821 25.2525......))', 4326));</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">The documentation at https://github.com/pgpointcloud/pointcloud mention using GIST as indexing;<br>
CREATE INDEX ON import USING GIST(PC_BoundingDiagonalGeometry(pa) gist_geometry_ops_nd);</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">So we drop btree and add gist indexing, run the same query takes 796,384 ms and return the two same records.</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">We expected btree on a integer to be relative slow but gist on a spatial type to be relative quick but the actual performance appears to be quiet similar.</p>
<div style="margin-top: 0px; margin-bottom: 0px;"></div>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">How does PC_Intersects function work? Does it need to decode the WellKnownBinary data inside pcpatch in order to determine the boundary? How does it use index?</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">Running "explain analyze select id from import where PC_Intersects(pa, st_geomfromtext('POLYGON ((6.9821 25.2525......))', 4326));"<br>
QUERY_PLAN | Seq Scan on "import" (cost=10000000000..10000014803 rows=3539 width=4)</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">It was interesting to see that the query used sequential scan of the table. We expected the query to use indexing and wanted to understand more about why this is happening.
<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">Maybe the structure of our SQL query is suboptimal or we have configured PostgresSQL poorly or the planner takes into account number of rows or amount of data.</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">Appreciate any input</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">kind regards, Lars</p>
<br>
</div>
</div>
</body>
</html>