[pgpointcloud] Patch min and max

[Lars]

Karl,

Interesting proposal.

The inital tests did however not yield any improved performance.

The table index is described like this (\d+ importdata)
"patch_min_max_idx" btree (pc_patchmin(pa, 'x'::text), pc_patchmax(pa, 'x'::text),pc_patchmin(pa, 'y'::text), pc_patchmax(pa, 'y'::text))

The explain query looks like this;
Seq Scan on public.importdata (cost=0.00..1402.50 rows=52321 width=132) (actual time=3.063..1546.160 rows=52321 loops=1)
Output: id, _pc_patchstat(pa, 0, 'x'::text), _pc_patchstat(pa, 0, 'y'::text), _pc_patchstat(pa, 1, 'x'::text), _pc_patchstat(pa, 1, 'y'::text)
Buffers: shared hit=795053 read=57146
Planning time= 0.600ms
Execution time= 1548.494 ms
(5 rows)

The "analyze importdata" command has also been invoked but did not make any difference.

Any ideas on why the expression does not improve performance? Viewing the explain output I notice "_pc_patchstat" which make me think that the query is using the index but it might be wishfull thinking on my part.

Kind regards, Lars


________________________________
Fra: Karl Pietrzak <kap4020 at gmail.com>
Sendt: 31. mai 2018 17:18
Til: laasunde at hotmail.com
Kopi: pgpointcloud at lists.osgeo.org
Emne: Re: [pgpointcloud] Patch min and max

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<mailto: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<mailto: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/20180601/3bdcbd49/attachment.html>