[pgpointcloud] PC_Intersects and indexing

[Lars]

Forgot to mention that we did run vacuum analyze without seeing much improvement.

Using PostgresSQL 10.3 and Postgis 2.4.4 on Windows 7.
________________________________
Fra: pgpointcloud <pgpointcloud-bounces at lists.osgeo.org> på vegne av Lars <laasunde at hotmail.com>
Sendt: 2. mai 2018 14:01
Til: pgpointcloud at lists.osgeo.org
Emne: [pgpointcloud] PC_Intersects and indexing


Hello,


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.


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.


Running the following query takes 776,952 ms and returns two records.
select id from import where PC_Intersects(pa, st_geomfromtext('POLYGON ((6.9821 25.2525......))', 4326));


The documentation at https://github.com/pgpointcloud/pointcloud mention using GIST as indexing;
CREATE INDEX ON import USING GIST(PC_BoundingDiagonalGeometry(pa) gist_geometry_ops_nd);


So we drop btree and add gist indexing, run the same query takes 796,384 ms and return the two same records.


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.


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?


Running "explain analyze select id from import where PC_Intersects(pa, st_geomfromtext('POLYGON ((6.9821 25.2525......))', 4326));"
QUERY_PLAN | Seq Scan on "import" (cost=10000000000..10000014803 rows=3539 width=4)


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.


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.


Appreciate any input


kind regards, Lars

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgpointcloud/attachments/20180502/64e29cbf/attachment-0001.html>