<div dir="ltr">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. <br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Jul 23, 2021 at 2:42 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_9087328465473828203WordSection1">
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">Hi everybody,<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">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.
<u></u><u></u></span></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.<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_9087328465473828203MsoListParagraph"><u></u><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US"><span>1.<span style="font:7pt "Times New Roman"">
</span></span></span><u></u><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">what are we missing?
<u></u><u></u></span></p>
<p class="gmail-m_9087328465473828203MsoListParagraph"><u></u><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US"><span>2.<span style="font:7pt "Times New Roman"">
</span></span></span><u></u><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)?
<u></u><u></u></span></p>
<p class="gmail-m_9087328465473828203MsoListParagraph"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">[o_N _]rid_seq
<u></u><u></u></span></p>
<p class="gmail-m_9087328465473828203MsoListParagraph"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">[o_N_]st_convexhull_idx<u></u><u></u></span></p>
<p class="gmail-m_9087328465473828203MsoListParagraph"><span style="font-size:10pt;font-family:"Arial",sans-serif" lang="EN-US">[o_N_]pkey<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">PS: there's no views nor mat-views in this cluster, so the hardlink-upgrade went quite straight through.<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">Thanks a lot<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">Igor and Luzian<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">Amt Geoinformation Kanton Thurgau, Switzerland<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"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">Details:<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">before:<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">PostgreSQL 9.6.21<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">POSTGIS="2.4.4 r16526"
<u></u><u></u></span></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"
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">PROJ="Rel. 4.9.3, 15 August 2016"
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">GDAL="GDAL 2.2.3, released 2017/11/20"
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">LIBXML="2.9.4" LIBJSON="0.12.1"
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">LIBPROTOBUF="1.2.1"
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">RASTER<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"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">after upgrade:<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">PostgreSQL 12.7<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">POSTGIS="3.1.2 cbe925d" [EXTENSION]
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">PGSQL="120"
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">GEOS="3.7.1-CAPI-1.11.1 27a5e771"
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">PROJ="Rel. 4.9.3, 15 August 2016"
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">GDAL="GDAL 2.2.3, released 2017/11/20"
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">LIBXML="2.9.4"
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">LIBJSON="0.12.1"
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)"
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">RASTER<u></u><u></u></span></p>
<div style="border-color:currentcolor currentcolor windowtext;border-style:none none solid;border-width:medium medium 1pt;padding:0cm 0cm 1pt">
<p class="MsoNormal" style="border:medium none;padding:0cm"><span style="font-size:10pt;font-family:"Arial",sans-serif"><u></u> <u></u></span></p>
<p class="MsoNormal" style="border:medium none;padding:0cm"><span style="font-size:10pt;font-family:"Arial",sans-serif"><u></u> <u></u></span></p>
<p class="MsoNormal" style="border:medium none;padding:0cm"><span style="font-size:10pt;font-family:"Arial",sans-serif">Command without casting:<u></u><u></u></span></p>
</div>
<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">EXPLAIN<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">WITH …<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> cells AS<u></u><u></u></span></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<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> FROM hoehendaten.swisssurface3d_raster, points2d p<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> WHERE ST_Intersects(rast, p.geom)),<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"><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">Nested Loop (cost=0.00..207960171.18 rows=11032199 width=32)<u></u><u></u></span></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)<u></u><u></u></span></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)<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">JIT:<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> Functions: 11<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> Options: Inlining true, Optimization true, Expressions true, Deforming true<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">(9 rows)<u></u><u></u></span></p>
<div style="border-color:currentcolor currentcolor windowtext;border-style:none none solid;border-width:medium medium 1pt;padding:0cm 0cm 1pt">
<p class="MsoNormal" style="border:medium none;padding:0cm"><span style="font-size:10pt;font-family:"Arial",sans-serif"><u></u> <u></u></span></p>
</div>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"><u></u> <u></u></span></p>
<div style="border-color:currentcolor currentcolor windowtext;border-style:none none solid;border-width:medium medium 1pt;padding:0cm 0cm 1pt">
<p class="MsoNormal" style="border:medium none;padding:0cm"><span style="font-size:10pt;font-family:"Arial",sans-serif">Command with casting:<u></u><u></u></span></p>
</div>
<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">EXPLAIN<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">WITH
<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"> cells AS<u></u><u></u></span></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<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> FROM hoehendaten.swisssurface3d_raster, points2d p<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> WHERE ST_Intersects(rast::geometry, p.geom)),<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"><u></u> <u></u></span></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)<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> -> ProjectSet (cost=0.00..1.02 rows=200 width=36)<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> -> Result (cost=0.00..0.01 rows=1 width=0)<u></u><u></u></span></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)<u></u><u></u></span></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))<u></u><u></u></span></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))<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">JIT:<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> Functions: 11<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif"> Options: Inlining false, Optimization false, Expressions true, Deforming true<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Arial",sans-serif">(9 rows)<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>
</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>