[pgpointcloud] Patch min and max

[Karl Pietrzak]

Postgres supports indices on expressions.
https://www.postgresql.org/docs/current/static/indexes-expressional.html

Would this feature support your use case?

Something like this:

CREATE INDEX patch_min_and_max_idx ON importdata (PC_PatchMin(pa,
'x'), PC_PatchMax(pa, 'x'),PC_PatchMin(pa, 'y'), PC_PatchMax(pa,
'y'));



On Thu, May 31, 2018 at 9:46 AM Lars <laasunde at hotmail.com> wrote:

> Hello,
>
> The below query takes 1,46 seconds using table with 50 000 patches and at
> least 5 seconds using table with 160 000 patches.
> "select id, PC_PatchMin(pa, 'x'), PC_PatchMax(pa, 'x'),PC_PatchMin(pa,
> 'y'), PC_PatchMax(pa, 'y') from importdata;"
>
> The table size has a significant effect on the query execution speed which
> means the query does not scale well.
>
> How can we make the query more scalable and/or improve query performance?
>
> One approach is to create an additional table that contains min/max values
> for each patch to prevent the "expensive" PC_PatchXXX function. This is
> obviously duplicate information that adds complexity to the system.
>
> Using Windows 10 and PostgreSQL 10.3
>
> Kind regard, Lars
> _______________________________________________
> pgpointcloud mailing list
> pgpointcloud at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/pgpointcloud



-- 
Karl
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgpointcloud/attachments/20180531/0415b891/attachment.html>