[pgpointcloud] PC_Intersects and indexing

[Éric Lemoine]

On Wed, 2018-05-02 at 12:01 +0000, Lars wrote:
> 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);

PC_Intersects uses PC_EnvelopeGeometry so you index should be based on
that function instead of PC_BoundingDiagonalGeometry:

CREATE INDEX ON patches USING GIST(PC_EnvelopeGeometry(pa));

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

You did not need to drop the btree index on the id column, but you may
not need to keep neither :)


-- 
Éric Lemoine
Oslandia
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 488 bytes
Desc: This is a digitally signed message part
URL: <http://lists.osgeo.org/pipermail/pgpointcloud/attachments/20180502/02831184/attachment.sig>