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

[Giunta Igor]

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)

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20210723/837bf2ab/attachment.html>