[postgis-users] planner missing indices on raster tables after upgrade to PostGIS-3

[Paul Ramsey]

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?)
P

On Mon, Jul 26, 2021 at 12:04 AM Giunta Igor <Igor.Giunta at tg.ch> wrote:

> Thank you Paul
>
>
>
> we weren't aware of this new planner capability, we tested it also with a
> bunch of other new interesting planning parameters (postgresql.conf).
>
>
>
> Someone in the meanwhile however pointed out, that we were comparing the
> outcome of two different functions:
>
>
>
> With cast:
>
> ST_Intersects( **geometry** geomA , geometry geomB )
>
> https://postgis.net/docs/manual-3.1/ST_Intersects.html
>
> which by default makes use of indices.
>
>
>
> Without cast:
>
> ST_Intersects( **raster** rastA , raster rastB )
>
> https://postgis.net/docs/manual-3.1/RT_ST_Intersects.html
>
> which tries to use the indices, but without guarantee.
>
>
>
> Can we do anything other than adding the cast in the request?
>
> Can an explicit warning be added in the manual for helping other users
> maybe?
>
>
>
> Thank you
>
> Igor
>
>
>
>
>
>
>
> *Von:* postgis-users <postgis-users-bounces at lists.osgeo.org> *Im Auftrag
> von *Paul Ramsey
> *Gesendet:* Sonntag, 25. Juli 2021 00:52
> *An:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Betreff:* Re: [postgis-users] planner missing indices on raster tables
> after upgrade to PostGIS-3
>
>
>
> 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.
>
>
>
> On Fri, Jul 23, 2021 at 2:42 AM Giunta Igor <Igor.Giunta at tg.ch> wrote:
>
> Hi everybody,
>
>
>
> 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.
>
> The weird thing is that if we force to cast ("::geometry", see below) then
> it suddenly properly works as expected.
>
>
>
> 1.      what are we missing?
>
> 2.      would be a solution to rebuild all indeces produced by
> raster2pgsql over all view-factors (N)?
>
> [o_N _]rid_seq
>
> [o_N_]st_convexhull_idx
>
> [o_N_]pkey
>
>
>
> PS: there's no views nor mat-views in this cluster, so the
> hardlink-upgrade went quite straight through.
>
>
>
> Thanks a lot
>
> Igor and Luzian
>
> Amt Geoinformation Kanton Thurgau, Switzerland
>
>
>
>
>
> Details:
>
>
>
> before:
>
> PostgreSQL 9.6.21
>
> POSTGIS="2.4.4 r16526"
>
> PGSQL="96" GEOS="3.7.1-CAPI-1.11.1 27a5e771"
>
> PROJ="Rel. 4.9.3, 15 August 2016"
>
> GDAL="GDAL 2.2.3, released 2017/11/20"
>
> LIBXML="2.9.4" LIBJSON="0.12.1"
>
> LIBPROTOBUF="1.2.1"
>
> RASTER
>
>
>
>
>
> after upgrade:
>
> PostgreSQL 12.7
>
> POSTGIS="3.1.2 cbe925d" [EXTENSION]
>
> PGSQL="120"
>
> GEOS="3.7.1-CAPI-1.11.1 27a5e771"
>
> PROJ="Rel. 4.9.3, 15 August 2016"
>
> GDAL="GDAL 2.2.3, released 2017/11/20"
>
> LIBXML="2.9.4"
>
> LIBJSON="0.12.1"
>
> LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)"
>
> RASTER
>
>
>
>
>
> Command without casting:
>
>
>
> EXPLAIN
>
> WITH …
>
>   cells AS
>
>     (SELECT p.geom AS geom, ST_Value(rast, 1, p.geom) AS val
>
>    FROM hoehendaten.swisssurface3d_raster, points2d p
>
>     WHERE ST_Intersects(rast, p.geom)),
>
> …;
>
>
>
>
>
> Nested Loop  (cost=0.00..207960171.18 rows=11032199 width=32)
>
>    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)
>
>    ->  Seq Scan on swisssurface3d_raster  (cost=0.00..3204.83 rows=165483
> width=18)
>
> …
>
> JIT:
>
>    Functions: 11
>
>    Options: Inlining true, Optimization true, Expressions true, Deforming
> true
>
> (9 rows)
>
>
>
>
>
> Command with casting:
>
>
>
> EXPLAIN
>
> WITH
>
> ….
>
>   cells AS
>
>     (SELECT p.geom AS geom, ST_Value(rast, 1, p.geom) AS val
>
>    FROM hoehendaten.swisssurface3d_raster, points2d p
>
>     WHERE ST_Intersects(rast::geometry, p.geom)),
>
> …;
>
>
>
> Nested Loop  (cost=1.66..336756.02 rows=33097 width=32)
>
>    ->  ProjectSet  (cost=0.00..1.02 rows=200 width=36)
>
>          ->  Result  (cost=0.00..0.01 rows=1 width=0)
>
>    ->  Index Scan using swisssurface3d_raster_st_convexhull_idx on
> swisssurface3d_raster  (cost=1.66..497.90 rows=17 width=18)
>
>          Index Cond: ((rast)::geometry &&
> st_geometryn(st_locatealong(('01020000600808000002000000000000000BC74441000000000F79334100000000000000000000000065D1444100000000D94F33415B8A6DE43A0FC740'::geometry),
> ((((generate_series(0, 11806000, 59032)))::numeric / 1000.0))::double
> precision, '0'::double precision), 1))
>
>          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))
>
> JIT:
>
>    Functions: 11
>
>    Options: Inlining false, Optimization false, Expressions true,
> Deforming true
>
> (9 rows)
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20210726/f40ea864/attachment.html>