Re: Slower queries on geometry_columns on 3.7.0beta1

Thank you Regina, this change solves the problem in my database as well!

For comparison, below are the results from one of my databases, where
geometry_columns is your new version and I created a version of the view
using the definition from version 3.6.

Since the new view is a bit more complex than the 3.6 view, the factor ~2
difference seems very reasonable to me.

dps=# select count(*) from geometry_columns;
 count
-------
  1389
(1 row)

Time: 59.236 ms
dps=# select count(*) from geometry_columns_36;
 count
-------
  1389
(1 row)

Time: 28.455 ms

/Fredrik


On Tue, Aug 4, 2026 at 8:43 PM Regina Obe <[email protected]> wrote:

> Fredrik,
>
>
>
> I think the remaining culprit was the feature addition to recurse views to
> get the base geometry type.  After I took that out my tests took
> significantly less time.
>
> I’ve backed out that feature as discussed here -
> https://trac.osgeo.org/postgis/ticket/1705
>
>
>
> Can you give this version a try.
>
>
>
>
>
> *From:* Fredrik Widlert <[email protected]>
> *Sent:* Tuesday, August 4, 2026 5:30 AM
> *To:* Paragon Corporation <[email protected]>
> *Cc:* [email protected]
> *Subject:* Re: Slower queries on geometry_columns on 3.7.0beta1
>
>
>
> Hi, thanks for looking into it!
>
>
>
> I've tried running a "create or replace view" with the new version
> containing AS NOT MATERIALIZED, but it does not seem to fix the problem in
> my test database (although it does make the query significantly faster when
> I test with the script to reproduce the problem in an empty database).
>
>
>
> In a test database where I've installed some of my real schemas, it looks
> like this:
>
> dps=# select count(*) from pg_tables;
>  count
> -------
>    473
> (1 row)
>
> dps=# select count(*) from pg_constraint;
>  count
> -------
>   3160
> (1 row)
>
> dps=# \timing
> Timing is on.
> dps=# select count(*) from geometry_columns ;
>  count
> -------
>    109
> (1 row)
>
> Time: 30722.082 ms (00:30.722)
> dps=#
> dps=# explain (analyze, buffers) select count(*) from geometry_columns ;
>
> The result of the explain is here:
> https://explain.depesz.com/s/e2q2
>
> I think most of my constraints are just "not null" constraints unrelated
> to geometries:
>
> dps=# select count(*) from pg_constraint where conname like '%not_null%';
>  count
> -------
>   2524
> (1 row)
>
>
>
> Since the view in 3.7beta1 includes operations on ACLs and is much more
> complex than before,
>
> I guess it's not very surprising that it has become slower, but something
> still seems wrong here.
>
>
>
> Is there any other information I can provide to help diagnose the problem?
>
>
>
> /Fredrik
>
>
>
>
>
>
>
> On Sat, Aug 1, 2026 at 4:25 AM Paragon Corporation <[email protected]> wrote:
>
> 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
> top.
>
> As detailed here --
>
> https://trac.osgeo.org/postgis/changeset/9f52dd2f9eeb30f7bc5f774eefe280020f320c52/git
>
> On Thu, Jul 30, 2026 at 7:24 AM 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
> view takes a few milliseconds to run.
> >
> > I notice that the view is much more complicated in 3.7.0beta1 than what
> it was in earlier versions,
> > so I guess the change is related to the new PostGIS rather than the new
> PostgreSQL.
> >
> > 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
> reproduce something similar
> > in an empty database, but I'm not sure if it shows exactly the same
> problem 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
> nothing to
> >  * do with PostGIS.  The 3.7 geometry_columns definition builds
> constraint_defs
> >  * from every row in pg_constraint, calls pg_get_constraintdef() for
> each row,
> >  * and scans the result three times with regular expressions.
> Consequently,
> >  * unrelated constraints affect the time needed to inspect geometry
> columns.
> >  *
> >  * A partitioned table is used to populate pg_constraint without needing
> >  * thousands of CREATE TABLE statements: its 100 CHECK constraints are
> copied
> >  * 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
> inference
> >  * added to geometry_columns in 3.7.  Views over those tables also
> exercise
> >  * _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
> load.
> >  */
> >
> > CREATE EXTENSION postgis;
> >
> > DO $roles$
> > BEGIN
> > IF NOT EXISTS (
> > SELECT
> > FROM pg_catalog.pg_roles
> > WHERE rolname = 'slow_columns_reader'
> > ) THEN
> > CREATE ROLE slow_columns_reader NOLOGIN;
> > END IF;
> >
> > IF NOT EXISTS (
> > SELECT
> > FROM pg_catalog.pg_roles
> > WHERE rolname = '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
> into
> >  * 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 >= %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 => 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 := 't' || i;
> > view_name := '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
> the
> >  * 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 = c.oid
> > AND NOT a.attisdropped
> > JOIN pg_catalog.pg_namespace AS n
> > ON c.relnamespace = n.oid
> > JOIN pg_catalog.pg_type AS t
> > ON a.atttypid = t.oid
> > WHERE c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char",
> > 'f'::"char", 'p'::"char"])
> > AND c.relname <> 'raster_columns'
> > AND t.typname = '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
> can
> >  * 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 = 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 = '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 = 'slow_columns_repro';
> >
> > RESET ROLE;
> >
> >
> >
> >
> >
> >
>
>