<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
Karl,</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
Interesting proposal.</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
The inital tests did however <u><strong>not </strong></u>yield any improved performance.</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
The table index is described like this (\d+ importdata)</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
"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))</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
The explain query looks like this;</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
Seq Scan on public.importdata (cost=0.00..1402.50 rows=52321 width=132) (actual time=3.063..1546.160 rows=52321 loops=1)</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
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)</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
Buffers: shared hit=795053 read=57146</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
Planning time= 0.600ms</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
Execution time= 1548.494 ms</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
(5 rows)</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
The "analyze importdata" command has also been invoked but did not make any difference.</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
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.</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
Kind regards, Lars </div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<hr tabindex="-1" style="width: 98%; display: inline-block;">
<div id="divRplyFwdMsg" dir="ltr"><font color="#000000" face="Calibri, sans-serif" style="font-size: 11pt;"><b>Fra:</b> Karl Pietrzak <kap4020@gmail.com><br>
<b>Sendt:</b> 31. mai 2018 17:18<br>
<b>Til:</b> laasunde@hotmail.com<br>
<b>Kopi:</b> pgpointcloud@lists.osgeo.org<br>
<b>Emne:</b> Re: [pgpointcloud] Patch min and max</font>
<div> </div>
</div>
<meta content="text/html; charset=utf-8">
<div>
<div dir="ltr">Postgres supports indices on expressions. <a href="https://www.postgresql.org/docs/current/static/indexes-expressional.html">https://www.postgresql.org/docs/current/static/indexes-expressional.html</a>
<div><br>
</div>
<div>Would this feature support your use case?</div>
<div><br>
</div>
<div>Something like this:</div>
<div><br>
</div>
<div>
<pre class="x_gmail-programlisting" style="padding: 2ex; border-radius: 8px; border: 1px solid rgb(207, 207, 207); border-image: none; color: rgb(0, 0, 0); overflow: auto; font-size: 1.3em; margin-top: 2ex; margin-bottom: 2ex; margin-left: 2ex; background-color: rgb(247, 247, 247);">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'));</pre>
</div>
<div><br>
</div>
</div>
<br>
<div class="x_gmail_quote">
<div dir="ltr">On Thu, May 31, 2018 at 9:46 AM Lars <<a href="mailto:laasunde@hotmail.com">laasunde@hotmail.com</a>> wrote:<br>
</div>
<blockquote class="x_gmail_quote" style="margin: 0px 0px 0px 0.8ex; padding-left: 1ex; border-left-color: rgb(204, 204, 204); border-left-width: 1px; border-left-style: solid;">
<div dir="ltr">
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
Hello,</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
The below query takes 1,46 seconds using table with 50 000 patches and at least 5 seconds using table with 160 000 patches.</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
"select id, PC_PatchMin(pa, 'x'), PC_PatchMax(pa, 'x'),PC_PatchMin(pa, 'y'), PC_PatchMax(pa, 'y') from importdata;"</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
The table size has a significant effect on the query execution speed which means the query does not scale well.</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
How can we make the query more scalable and/or improve query performance?</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
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.</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
Using Windows 10 and PostgreSQL 10.3</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri,Helvetica,sans-serif; font-size: 12pt;">
Kind regard, Lars<br>
</div>
</div>
_______________________________________________<br>
pgpointcloud mailing list<br>
<a href="mailto:pgpointcloud@lists.osgeo.org" target="_blank">pgpointcloud@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/pgpointcloud" target="_blank" rel="noreferrer">https://lists.osgeo.org/mailman/listinfo/pgpointcloud</a></blockquote>
</div>
<br clear="all">
<div><br>
</div>
-- <br>
<div class="x_gmail_signature" dir="ltr">
<div dir="ltr">
<div>Karl</div>
</div>
</div>
</div>
</body>
</html>