<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{mso-style-priority:99;
mso-style-link:"Nur Text Zchn";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Arial",sans-serif;
mso-fareast-language:EN-US;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.gmail-m9087328465473828203msolistparagraph, li.gmail-m9087328465473828203msolistparagraph, div.gmail-m9087328465473828203msolistparagraph
{mso-style-name:gmail-m_9087328465473828203msolistparagraph;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.E-MailFormatvorlage19
{mso-style-type:personal-reply;
font-family:"Arial",sans-serif;
color:windowtext;
font-weight:normal;
font-style:normal;}
span.NurTextZchn
{mso-style-name:"Nur Text Zchn";
mso-style-priority:99;
mso-style-link:"Nur Text";
font-family:"Arial",sans-serif;}
.MsoChpDefault
{mso-style-type:export-only;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="DE-CH" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US">Thank you Paul<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US">we weren't aware of this new planner capability, we tested it also with a bunch of other new interesting planning parameters (postgresql.conf).<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US">Someone in the meanwhile however pointed out, that we were comparing the outcome of two different functions:<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">With cast: <o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">ST_Intersects( *<b>geometry</b>* geomA , geometry geomB )<o:p></o:p></span></p>
<p class="MsoPlainText"><a href="https://postgis.net/docs/manual-3.1/ST_Intersects.html"><span lang="EN-US">https://postgis.net/docs/manual-3.1/ST_Intersects.html</span></a><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US">which by default makes use of indices.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">Without cast: <o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">ST_Intersects( *<b>raster</b>* rastA , raster rastB )<o:p></o:p></span></p>
<p class="MsoPlainText"><a href="https://postgis.net/docs/manual-3.1/RT_ST_Intersects.html"><span lang="EN-US">https://postgis.net/docs/manual-3.1/RT_ST_Intersects.html</span></a><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US">which tries to use the indices, but without guarantee.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US">Can we do anything other than adding the cast in the request?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US">Can an explicit warning be added in the manual for helping other users maybe?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US">Thank you<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US">Igor<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span lang="DE" style="font-size:11.0pt;font-family:"Calibri",sans-serif">Von:</span></b><span lang="DE" style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users <postgis-users-bounces@lists.osgeo.org>
<b>Im Auftrag von </b>Paul Ramsey<br>
<b>Gesendet:</b> Sonntag, 25. Juli 2021 00:52<br>
<b>An:</b> PostGIS Users Discussion <postgis-users@lists.osgeo.org><br>
<b>Betreff:</b> Re: [postgis-users] planner missing indices on raster tables after upgrade to PostGIS-3<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">Pg12 added the capability of the planner breaking the CTE barrier and planning the whole query holistically. It's possible this is, in your case, actually leading to a worse plan, in the presence of an implicit cast. Check out the 'MATERIALIZED'
keyword for example. <o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">On Fri, Jul 23, 2021 at 2:42 AM Giunta Igor <<a href="mailto:Igor.Giunta@tg.ch">Igor.Giunta@tg.ch</a>> wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif">Hi everybody,</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif">After Upgrade to Postgresql-12 / PostGIS-3 (via hardlink), the planner is missing the indices on raster
tables, which have been produced some times ago by raster2pgsql. </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif">The weird thing is that if we force to cast ("::geometry", see below) then it suddenly properly works
as expected.</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="gmail-m9087328465473828203msolistparagraph"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif">1.</span><span lang="EN-US" style="font-size:7.0pt">
</span><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif">what are we missing?
</span><o:p></o:p></p>
<p class="gmail-m9087328465473828203msolistparagraph"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif">2.</span><span lang="EN-US" style="font-size:7.0pt">
</span><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif">would be a solution to rebuild all indeces produced by raster2pgsql over all view-factors (N)?
</span><o:p></o:p></p>
<p class="gmail-m9087328465473828203msolistparagraph"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif">[o_N _]rid_seq
</span><o:p></o:p></p>
<p class="gmail-m9087328465473828203msolistparagraph"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif">[o_N_]st_convexhull_idx</span><o:p></o:p></p>
<p class="gmail-m9087328465473828203msolistparagraph"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif">[o_N_]pkey</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif">PS: there's no views nor mat-views in this cluster, so the hardlink-upgrade went quite straight through.</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">Thanks a lot</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">Igor and Luzian</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">Amt Geoinformation Kanton Thurgau, Switzerland</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">Details:</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">before:</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">PostgreSQL 9.6.21</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">POSTGIS="2.4.4 r16526"
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">PGSQL="96" GEOS="3.7.1-CAPI-1.11.1 27a5e771"
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">PROJ="Rel. 4.9.3, 15 August 2016"
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">GDAL="GDAL 2.2.3, released 2017/11/20"
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">LIBXML="2.9.4" LIBJSON="0.12.1"
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">LIBPROTOBUF="1.2.1"
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">RASTER</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">after upgrade:</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">PostgreSQL 12.7</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">POSTGIS="3.1.2 cbe925d" [EXTENSION]
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">PGSQL="120"
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">GEOS="3.7.1-CAPI-1.11.1 27a5e771"
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">PROJ="Rel. 4.9.3, 15 August 2016"
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">GDAL="GDAL 2.2.3, released 2017/11/20"
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">LIBXML="2.9.4"
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">LIBJSON="0.12.1"
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)"
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">RASTER</span><o:p></o:p></p>
<div style="border:none;border-bottom:solid windowtext 1.0pt;padding:0cm 0cm 1.0pt 0cm;border-color:currentcolor currentcolor windowtext">
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">Command without casting:</span><o:p></o:p></p>
</div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">EXPLAIN</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">WITH …</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> cells AS</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> (SELECT p.geom AS geom, ST_Value(rast, 1, p.geom) AS val</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> FROM hoehendaten.swisssurface3d_raster, points2d p</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> WHERE ST_Intersects(rast, p.geom)),</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">…;</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">Nested Loop (cost=0.00..207960171.18 rows=11032199 width=32)</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> Join Filter: st_intersects(swisssurface3d_raster.rast, st_geometryn(st_locatealong(('01020000600808000002000000000000000BC74441000000000F79334100000000000000000000000065D1444100000000D94F33415B8A6DE43A0FC740'::geometry),
((((generate_series(0, 11806000, 59032)))::numeric / 1000.0))::double precision, '0'::double precision), 1), NULL::integer)</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> -> Seq Scan on swisssurface3d_raster (cost=0.00..3204.83 rows=165483 width=18)</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">…</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">JIT:</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> Functions: 11</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> Options: Inlining true, Optimization true, Expressions true, Deforming true</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">(9 rows)</span><o:p></o:p></p>
<div style="border:none;border-bottom:solid windowtext 1.0pt;padding:0cm 0cm 1.0pt 0cm;border-color:currentcolor currentcolor windowtext">
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
</div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<div style="border:none;border-bottom:solid windowtext 1.0pt;padding:0cm 0cm 1.0pt 0cm;border-color:currentcolor currentcolor windowtext">
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">Command with casting:</span><o:p></o:p></p>
</div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">EXPLAIN</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">WITH
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">….</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> cells AS</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> (SELECT p.geom AS geom, ST_Value(rast, 1, p.geom) AS val</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> FROM hoehendaten.swisssurface3d_raster, points2d p</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> WHERE ST_Intersects(rast::geometry, p.geom)),</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">…;</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">Nested Loop (cost=1.66..336756.02 rows=33097 width=32)</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> -> ProjectSet (cost=0.00..1.02 rows=200 width=36)</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> -> Result (cost=0.00..0.01 rows=1 width=0)</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> -> Index Scan using swisssurface3d_raster_st_convexhull_idx on swisssurface3d_raster (cost=1.66..497.90 rows=17
width=18)</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> Index Cond: ((rast)::geometry && st_geometryn(st_locatealong(('01020000600808000002000000000000000BC74441000000000F79334100000000000000000000000065D1444100000000D94F33415B8A6DE43A0FC740'::geometry),
((((generate_series(0, 11806000, 59032)))::numeric / 1000.0))::double precision, '0'::double precision), 1))</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> Filter: st_intersects((rast)::geometry, st_geometryn(st_locatealong(('01020000600808000002000000000000000BC74441000000000F79334100000000000000000000000065D1444100000000D94F33415B8A6DE43A0FC740'::geometry),
((((generate_series(0, 11806000, 59032)))::numeric / 1000.0))::double precision, '0'::double precision), 1))</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">JIT:</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> Functions: 11</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> Options: Inlining false, Optimization false, Expressions true, Deforming true</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif">(9 rows)</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:10.0pt;font-family:"Arial",sans-serif"> </span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal">_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p>
</blockquote>
</div>
</div>
</body>
</html>