Re: Slower queries on geometry_columns on 3.7.0beta1

I have the issue ticketed here -
https://trac.osgeo.org/postgis/ticket/6110  though I think it might
only affect constraint based tables.

Can you try this change, just add a AS NOT MATERIALIZED to the CTE at the t=
op.

As detailed here --
https://trac.osgeo.org/postgis/changeset/9f52dd2f9eeb30f7bc5f774eefe280020f=
320c52/git

On Thu, Jul 30, 2026 at 7:24=E2=80=AFAM Fredrik Widlert
<[email protected]> wrote:
>
> Hi, I tested postgis-3.7.0beta1 today on PostgreSQL 19 beta 2.
>
> It appears that selects on geometry_columns has become much slower in my =
small test database:
>
> select count(*) from geometry_columns;
>
> now takes more than two minutes. The SQL from the 3.6.0 version of the vi=
ew takes a few milliseconds to run.
>
> I notice that the view is much more complicated in 3.7.0beta1 than what i=
t was in earlier versions,
> so I guess the change is related to the new PostGIS rather than the new P=
ostgreSQL.
>
> Query plan for the new version here:
> https://explain.depesz.com/s/rtSg
>
> Is this a known problem? I had Codex build a test case that seems to repr=
oduce something similar
> in an empty database, but I'm not sure if it shows exactly the same probl=
em I'm encountering.
>
> Anyway, this test case is included below in case it is useful.
>
> On my machine, the testcase shows the 3.6 version taking 2 millis and the=
 3.7 version 11 seconds.
>
> /Fredrik Widlert
> [email protected]
>
>
>
> \set ON_ERROR_STOP on
> \timing on
>
> /*
>  * Reproducer for slow geometry_columns queries with the PostGIS 3.7
>  * definition.
>  *
>  * Run this with psql as a superuser in a new, otherwise empty database.
>  *
>  * The test deliberately creates many CHECK constraints which have nothin=
g to
>  * do with PostGIS.  The 3.7 geometry_columns definition builds constrain=
t_defs
>  * from every row in pg_constraint, calls pg_get_constraintdef() for each=
 row,
>  * and scans the result three times with regular expressions.  Consequent=
ly,
>  * unrelated constraints affect the time needed to inspect geometry colum=
ns.
>  *
>  * A partitioned table is used to populate pg_constraint without needing
>  * thousands of CREATE TABLE statements: its 100 CHECK constraints are co=
pied
>  * to each of 200 partitions, producing about 20,000 unrelated
>  * pg_constraint rows.
>  *
>  * The spatial tables use constraint-based geometry columns rather than
>  * typmods.  This is important because it exercises the constraint infere=
nce
>  * added to geometry_columns in 3.7.  Views over those tables also exerci=
se
>  * _postgis_geometry_columns_view_column_origin().
>  *
>  * On slower machines, lower the loop upper bounds below.  The product of=
 the
>  * CHECK-constraint and partition counts controls most of the catalog loa=
d.
>  */
>
> CREATE EXTENSION postgis;
>
> DO $roles$
> BEGIN
> IF NOT EXISTS (
> SELECT
> FROM pg_catalog.pg_roles
> WHERE rolname =3D 'slow_columns_reader'
> ) THEN
> CREATE ROLE slow_columns_reader NOLOGIN;
> END IF;
>
> IF NOT EXISTS (
> SELECT
> FROM pg_catalog.pg_roles
> WHERE rolname =3D 'slow_columns_user'
> ) THEN
> CREATE ROLE slow_columns_user NOLOGIN;
> END IF;
> END
> $roles$;
>
> GRANT slow_columns_reader TO slow_columns_user;
>
> CREATE SCHEMA slow_columns_repro;
>
> /*
>  * Create the unrelated catalog load first.  Constraints are added to the
>  * parent before its partitions are created so PostgreSQL copies them int=
o
>  * every partition.
>  */
> CREATE TABLE slow_columns_repro.constraint_noise (
> id integer NOT NULL
> ) PARTITION BY RANGE (id);
>
> DO $noise$
> BEGIN
> FOR i IN 1..100 LOOP
> EXECUTE format(
> 'ALTER TABLE slow_columns_repro.constraint_noise '
> 'ADD CONSTRAINT %I CHECK (id >=3D %s)',
> 'noise_check_' || i,
> -i
> );
> END LOOP;
>
> FOR i IN 0..199 LOOP
> EXECUTE format(
> 'CREATE TABLE slow_columns_repro.%I '
> 'PARTITION OF slow_columns_repro.constraint_noise '
> 'FOR VALUES FROM (%s) TO (%s)',
> 'constraint_noise_' || i,
> i,
> i + 1
> );
> END LOOP;
> END
> $noise$;
>
> /*
>  * AddGeometryColumn(..., use_typmod =3D> false) creates legacy CHECK
>  * constraints for dimensionality, SRID, and geometry type.  The example =
in
>  * slow_columns.sql used typmods and therefore did not exercise this path=
.
>  */
> DO $spatial_objects$
> DECLARE
> table_name text;
> view_name text;
> BEGIN
> FOR i IN 0..199 LOOP
> table_name :=3D 't' || i;
> view_name :=3D 'v' || i;
>
> EXECUTE format(
> 'CREATE TABLE slow_columns_repro.%I (id bigint)',
> table_name
> );
>
> PERFORM AddGeometryColumn(
> 'slow_columns_repro',
> table_name,
> 'shape',
> 5845,
> 'POINT',
> 3,
> false
> );
>
> EXECUTE format(
> 'CREATE VIEW slow_columns_repro.%I AS '
> 'SELECT id, shape FROM slow_columns_repro.%I',
> view_name,
> table_name
> );
> END LOOP;
> END
> $spatial_objects$;
>
> GRANT USAGE ON SCHEMA slow_columns_repro TO slow_columns_reader;
> GRANT SELECT ON ALL TABLES IN SCHEMA slow_columns_repro
> TO slow_columns_reader;
>
> /*
>  * Preserve the 3.6 definition under another name so both versions see th=
e
>  * exact same catalog and privileges.
>  */
> CREATE VIEW slow_columns_repro.geometry_columns_36 AS
> SELECT
> current_database()::varchar(256) AS f_table_catalog,
> n.nspname AS f_table_schema,
> c.relname AS f_table_name,
> a.attname AS f_geometry_column,
> COALESCE(postgis_typmod_dims(a.atttypmod), 2) AS coord_dimension,
> COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), 0) AS srid,
> replace(
> replace(
> COALESCE(
> NULLIF(upper(postgis_typmod_type(a.atttypmod)), 'GEOMETRY'),
> 'GEOMETRY'
> ),
> 'ZM',
> ''
> ),
> 'Z',
> ''
> )::varchar(30) AS type
> FROM pg_catalog.pg_class AS c
> JOIN pg_catalog.pg_attribute AS a
> ON a.attrelid =3D c.oid
> AND NOT a.attisdropped
> JOIN pg_catalog.pg_namespace AS n
> ON c.relnamespace =3D n.oid
> JOIN pg_catalog.pg_type AS t
> ON a.atttypid =3D t.oid
> WHERE c.relkind =3D ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char",
> 'f'::"char", 'p'::"char"])
> AND c.relname <> 'raster_columns'
> AND t.typname =3D 'geometry'
> AND NOT pg_catalog.pg_is_other_temp_schema(c.relnamespace)
> AND pg_catalog.has_table_privilege(c.oid, 'SELECT');
>
> GRANT SELECT ON slow_columns_repro.geometry_columns_36
> TO slow_columns_reader;
>
> /*
>  * Give the planner current catalog statistics.  Without this, results ca=
n
>  * depend on whether autovacuum happened to run during fixture creation.
>  */
> ANALYZE pg_catalog.pg_class;
> ANALYZE pg_catalog.pg_attribute;
> ANALYZE pg_catalog.pg_constraint;
> ANALYZE pg_catalog.pg_namespace;
> ANALYZE pg_catalog.pg_rewrite;
> ANALYZE pg_catalog.pg_type;
>
> SET ROLE slow_columns_user;
> SET jit =3D off;
>
> \echo
> \echo 'Catalog size used by the reproducer:'
> SELECT count(*) AS constraint_count
> FROM pg_catalog.pg_constraint;
>
> \echo
> \echo 'PostGIS 3.6 geometry_columns definition (baseline):'
> EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)
> SELECT count(*)
> FROM slow_columns_repro.geometry_columns_36
> WHERE f_table_schema =3D 'slow_columns_repro';
>
> \echo
> \echo 'Installed PostGIS geometry_columns definition (3.7 regression):'
> EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)
> SELECT count(*)
> FROM public.geometry_columns
> WHERE f_table_schema =3D 'slow_columns_repro';
>
> RESET ROLE;
>
>
>
>
>
>