[pgpointcloud] PC_Intersects and indexing

[Lars]

Eric,

You suggestion improved performance by factor of 20. Thank you 😊

Maybe consider adding indexing command to pgpointcloud website.

________________________________
Fra: Éric Lemoine <eric.lemoine at oslandia.com>
Sendt: 2. mai 2018 15:23
Til: Lars; pgpointcloud at lists.osgeo.org
Emne: Re: [pgpointcloud] PC_Intersects and indexing

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 --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgpointcloud/attachments/20180507/c5d949e4/attachment.html>