[pgpointcloud] PC_Get array of dimensions

[Regina Obe]

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>