<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;">
The query being used is "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;">
This information is used to make bounding boxes for <u><strong>all patches</strong></u> in a table. There is no where clause in our query, we want all rows. Does the index expression help "pre-compute" the PC_PatchMin/Max values for quicker access or does it
only work when using a where clause?</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> fredag 1. juni 2018 12.44<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">Based on the "Seq Scan on public.importdata", it doesn't look like the index is being used. :(
<div><br>
</div>
<div>It's almost impossible to replicate this, because we don't have any specifics. If you could show us the exact, complete statements you used to create the table, create the index, etc. and the SELECT you are doing, that would be very helpful.</div>
</div>
<br>
<div class="x_gmail_quote">
<div dir="ltr">On Fri, Jun 1, 2018 at 4:10 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;">
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 style="width: 98%; display: inline-block;">
<div id="x_m_-6325196892831888957divRplyFwdMsg" dir="ltr"><font color="#000000" face="Calibri, sans-serif" style="font-size: 11pt;"><b>Fra:</b> Karl Pietrzak <<a href="mailto:kap4020@gmail.com" target="_blank">kap4020@gmail.com</a>><br>
<b>Sendt:</b> 31. mai 2018 17:18<br>
<b>Til:</b> <a href="mailto:laasunde@hotmail.com" target="_blank">laasunde@hotmail.com</a><br>
<b>Kopi:</b> <a href="mailto:pgpointcloud@lists.osgeo.org" target="_blank">pgpointcloud@lists.osgeo.org</a><br>
<b>Emne:</b> Re: [pgpointcloud] Patch min and max</font>
<div> </div>
</div>
<div>
<div dir="ltr">Postgres supports indices on expressions. <a href="https://www.postgresql.org/docs/current/static/indexes-expressional.html" target="_blank">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_m_-6325196892831888957x_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_m_-6325196892831888957x_gmail_quote">
<div dir="ltr">On Thu, May 31, 2018 at 9:46 AM Lars <<a href="mailto:laasunde@hotmail.com" target="_blank">laasunde@hotmail.com</a>> wrote:<br>
</div>
<blockquote class="x_m_-6325196892831888957x_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_m_-6325196892831888957x_gmail_signature" dir="ltr">
<div dir="ltr">
<div>Karl</div>
</div>
</div>
</div>
</div>
</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>