Slower queries on geometry_columns on 3.7.0beta1

[Fredrik Widlert]

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



\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/20260730/2db9994a/attachment.htm>