<div dir="ltr"><div>No, you'll have to add the cast. Some non-trivial renovation of the raster type is required in order to modernize the index usage for recent versions, so that will be quite some time coming (3.3, 2022?)<br></div>P<br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Jul 26, 2021 at 12:04 AM Giunta Igor <<a href="mailto:Igor.Giunta@tg.ch">Igor.Giunta@tg.ch</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div lang="DE-CH">
<div class="gmail-m_8548674202425112807WordSection1">
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">Thank you Paul<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="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).<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">Someone in the meanwhile however pointed out, that we were comparing the outcome of two different functions:<u></u><u></u></span></p>
<p class="gmail-m_8548674202425112807MsoPlainText"><span lang="EN-US"><u></u> <u></u></span></p>
<p class="gmail-m_8548674202425112807MsoPlainText"><span lang="EN-US">With cast: <u></u><u></u></span></p>
<p class="gmail-m_8548674202425112807MsoPlainText"><span lang="EN-US">ST_Intersects( *<b>geometry</b>* geomA , geometry geomB )<u></u><u></u></span></p>
<p class="gmail-m_8548674202425112807MsoPlainText"><a href="https://postgis.net/docs/manual-3.1/ST_Intersects.html" target="_blank"><span lang="EN-US">https://postgis.net/docs/manual-3.1/ST_Intersects.html</span></a><span lang="EN-US"><u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">which by default makes use of indices.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US"><u></u> <u></u></span></p>
<p class="gmail-m_8548674202425112807MsoPlainText"><span lang="EN-US">Without cast: <u></u><u></u></span></p>
<p class="gmail-m_8548674202425112807MsoPlainText"><span lang="EN-US">ST_Intersects( *<b>raster</b>* rastA , raster rastB )<u></u><u></u></span></p>
<p class="gmail-m_8548674202425112807MsoPlainText"><a href="https://postgis.net/docs/manual-3.1/RT_ST_Intersects.html" target="_blank"><span lang="EN-US">https://postgis.net/docs/manual-3.1/RT_ST_Intersects.html</span></a><span lang="EN-US"><u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">which tries to use the indices, but without guarantee.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">Can we do anything other than adding the cast in the request?<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">Can an explicit warning be added in the manual for helping other users maybe?<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">Thank you<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">Igor<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><b><span style="font-size:11pt;font-family:"Calibri",sans-serif" lang="DE">Von:</span></b><span style="font-size:11pt;font-family:"Calibri",sans-serif" lang="DE"> postgis-users <<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>>
<b>Im Auftrag von </b>Paul Ramsey<br>
<b>Gesendet:</b> Sonntag, 25. Juli 2021 00:52<br>
<b>An:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
<b>Betreff:</b> Re: [postgis-users] planner missing indices on raster tables after upgrade to PostGIS-3<u></u><u></u></span></p>
<p class="MsoNormal"><u></u> <u></u></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. <u></u><u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal">On Fri, Jul 23, 2021 at 2:42 AM Giunta Igor <<a href="mailto:Igor.Giunta@tg.ch" target="_blank">Igor.Giunta@tg.ch</a>> wrote:<u></u><u></u></p>
</div>
<blockquote style="border-color:currentcolor currentcolor currentcolor rgb(204,204,204);border-style:none none none solid;border-width:medium medium medium 1pt;padding:0cm 0cm 0cm 6pt;margin-left:4.8pt;margin-right:0cm">
<div>
<div>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">Hi everybody,</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">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><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">The weird thing is that if we force to cast ("::geometry", see below) then it suddenly properly works
as expected.</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US"> </span><u></u><u></u></p>
<p class="gmail-m_8548674202425112807gmail-m9087328465473828203msolistparagraph"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">1.</span><span style="font-size:7pt" lang="EN-US">
</span><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">what are we missing?
</span><u></u><u></u></p>
<p class="gmail-m_8548674202425112807gmail-m9087328465473828203msolistparagraph"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">2.</span><span style="font-size:7pt" lang="EN-US">
</span><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">would be a solution to rebuild all indeces produced by raster2pgsql over all view-factors (N)?
</span><u></u><u></u></p>
<p class="gmail-m_8548674202425112807gmail-m9087328465473828203msolistparagraph"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">[o_N _]rid_seq
</span><u></u><u></u></p>
<p class="gmail-m_8548674202425112807gmail-m9087328465473828203msolistparagraph"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">[o_N_]st_convexhull_idx</span><u></u><u></u></p>
<p class="gmail-m_8548674202425112807gmail-m9087328465473828203msolistparagraph"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">[o_N_]pkey</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">PS: there's no views nor mat-views in this cluster, so the hardlink-upgrade went quite straight through.</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">Thanks a lot</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">Igor and Luzian</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">Amt Geoinformation Kanton Thurgau, Switzerland</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">Details:</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">before:</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">PostgreSQL 9.6.21</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">POSTGIS="2.4.4 r16526"
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">PGSQL="96" GEOS="3.7.1-CAPI-1.11.1 27a5e771"
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">PROJ="Rel. 4.9.3, 15 August 2016"
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">GDAL="GDAL 2.2.3, released 2017/11/20"
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">LIBXML="2.9.4" LIBJSON="0.12.1"
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">LIBPROTOBUF="1.2.1"
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">RASTER</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">after upgrade:</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">PostgreSQL 12.7</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">POSTGIS="3.1.2 cbe925d" [EXTENSION]
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">PGSQL="120"
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">GEOS="3.7.1-CAPI-1.11.1 27a5e771"
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">PROJ="Rel. 4.9.3, 15 August 2016"
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">GDAL="GDAL 2.2.3, released 2017/11/20"
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">LIBXML="2.9.4"
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">LIBJSON="0.12.1"
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)"
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">RASTER</span><u></u><u></u></p>
<div style="border-style:none none solid;border-width:medium medium 1pt;padding:0cm 0cm 1pt;border-color:currentcolor currentcolor windowtext">
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">Command without casting:</span><u></u><u></u></p>
</div>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">EXPLAIN</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">WITH …</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> cells AS</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> (SELECT p.geom AS geom, ST_Value(rast, 1, p.geom) AS val</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> FROM hoehendaten.swisssurface3d_raster, points2d p</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> WHERE ST_Intersects(rast, p.geom)),</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">…;</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">Nested Loop (cost=0.00..207960171.18 rows=11032199 width=32)</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;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><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> -> Seq Scan on swisssurface3d_raster (cost=0.00..3204.83 rows=165483 width=18)</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">…</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">JIT:</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> Functions: 11</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> Options: Inlining true, Optimization true, Expressions true, Deforming true</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">(9 rows)</span><u></u><u></u></p>
<div style="border-style:none none solid;border-width:medium medium 1pt;padding:0cm 0cm 1pt;border-color:currentcolor currentcolor windowtext">
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
</div>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<div style="border-style:none none solid;border-width:medium medium 1pt;padding:0cm 0cm 1pt;border-color:currentcolor currentcolor windowtext">
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">Command with casting:</span><u></u><u></u></p>
</div>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">EXPLAIN</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">WITH
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">….</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> cells AS</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> (SELECT p.geom AS geom, ST_Value(rast, 1, p.geom) AS val</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> FROM hoehendaten.swisssurface3d_raster, points2d p</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> WHERE ST_Intersects(rast::geometry, p.geom)),</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">…;</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">Nested Loop (cost=1.66..336756.02 rows=33097 width=32)</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> -> ProjectSet (cost=0.00..1.02 rows=200 width=36)</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> -> Result (cost=0.00..0.01 rows=1 width=0)</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;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><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;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><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;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><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">JIT:</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> Functions: 11</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> Options: Inlining false, Optimization false, Expressions true, Deforming true</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">(9 rows)</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> </span><u></u><u></u></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><u></u><u></u></p>
</blockquote>
</div>
</div>
</div>
_______________________________________________<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" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div>