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

[Giunta Igor]

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<mailto: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<mailto: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/4686c863/attachment.html>