Slower queries on geometry_columns on 3.7.0beta1

Tue Aug 4 11:43:08 PDT 2026

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 <fredrik.widlert at digpro.se> 
Sent: Tuesday, August 4, 2026 5:30 AM
To: Paragon Corporation <lr at pcorp.us>
Cc: postgis-users at lists.osgeo.org
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 <lr at pcorp.us <mailto:lr at pcorp.us> > 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
<fredrik.widlert at digpro.se <mailto:fredrik.widlert at digpro.se> > 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
> fredrik.widlert at digpro.se <mailto:fredrik.widlert at digpro.se> 
>
>
>
> \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;
>
>
>
>
>
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20260804/075a59e5/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: geometry_columns.sql
Type: application/octet-stream
Size: 4484 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20260804/075a59e5/attachment.obj>