[pgpointcloud] PC_Get array of dimensions
Regina Obe
lr at pcorp.us
Wed May 9 01:22:17 PDT 2018
That's surprising. It should have had a significant performance gain
though it's possible the PostgreSQL 10 rewrote your original so it ends up
following the lateral construct.
I think older versions just end up calling PC_Explode multiple times.
What does your final query look like?
From: Lars [mailto:laasunde at hotmail.com]
Sent: Wednesday, May 09, 2018 2:19 AM
To: Regina Obe <lr at pcorp.us>; pgpointcloud at lists.osgeo.org
Subject: Sv: [pgpointcloud] PC_Get array of dimensions
Regina,
Thank you for the suggestion.
Using LATERAL makes the query easier to read but it does not appear to have
any performance gain.
_____
Fra: Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> >
Sendt: 8. mai 2018 14:45
Til: 'Lars'; pgpointcloud at lists.osgeo.org
<mailto:pgpointcloud at lists.osgeo.org>
Emne: RE: [pgpointcloud] PC_Get array of dimensions
Lars,
Have you tried using LATERAL for this. This seems like an example just
dying for that to be used.
For example
SELECT PC_Get(pae, 'X') AS X, PC_Get(pae, 'Y') AS Y, PC_GET(pae, 'Z') As Z
FROM import , LATERAL PC_Explode(import.pa) AS pae
WHERE PC_Intersects(...);
From: pgpointcloud [mailto:pgpointcloud-bounces at lists.osgeo.org] On Behalf
Of Lars
Sent: Tuesday, May 08, 2018 5:32 AM
To: pgpointcloud at lists.osgeo.org <mailto:pgpointcloud at lists.osgeo.org>
Subject: [pgpointcloud] PC_Get array of dimensions
Hello,
Does pgpointcloud have any plans to add a generic PC_Get function that
accepts an array of input names? There was a discussion on the subject a
while back but cannot see any new function.
http://lists.osgeo.org/pipermail/pdal/2014-November/000429.html
The suggested function would make the query more compact and easier to read.
Could it also improve performance?
A simple query without PC_Get/PC_Explode takes 1 ms.
select id from import where PC_Intersects(...);
Updating the test to include X value and the time increases to 21 ms.
select PC_Get(PC_Explode(pa), 'X') from import where PC_Intersects(...);
Updating the test to include X and Y and it takes 34ms.
select PC_Get(PC_Explode(pa), 'X'), PC_Get(PC_Explode(pa), 'Y') from import
where PC_Intersects(...);
Updating the test to include X,Y and Z and it takes 46ms.
select PC_Get(PC_Explode(pa), 'X'), PC_Get(PC_Explode(pa), 'Y'),
PC_Get(PC_Explode(pa), 'Z') from import where PC_Intersects(...);
It appear that for each dimension added the time increase quiet a bit. This
is obviously a very simple test but just wanted to get a feedback.
Using Windows 7, PostgresSQL 10.3 and PostGIS 2.4.4
kind regards, Lars
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgpointcloud/attachments/20180509/bc18c669/attachment-0001.html>
More information about the pgpointcloud
mailing list