Slower queries on geometry_columns on 3.7.0beta1
Fredrik Widlert <[email protected]> Thu, 30 Jul 2026 13:24:30 +0200
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <CADfhSr_wT+4q4Jqq6Ut8qaDDiVnnpUuCufdbQt5iKWhwHSZOTQ@mail.gmail.com> |
--0000000000007a1b150657d2513f Content-Type: text/plain; charset="UTF-8" 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; --0000000000007a1b150657d2513f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">Hi, I tested postgis-3.7.0beta1 today on PostgreSQL 19 bet= a 2.<br><br>It appears that selects on geometry_columns has become much slo= wer in my small test database:<br><br>select count(*) from geometry_columns= ;<br><br>now takes more than two minutes. The SQL from the 3.6.0 version of= the view takes a few milliseconds to run.<br><br>I notice that the view is= much more complicated in 3.7.0beta1 than what it was in earlier versions,<= br>so I guess the change is related to the new PostGIS rather than the new = PostgreSQL.<br><br>Query plan for the new version here:<br><a href=3D"https= ://explain.depesz.com/s/rtSg">https://explain.depesz.com/s/rtSg</a><br><br>= Is this a known problem? I had Codex build a test case that seems to reprod= uce something similar<br>in an empty database, but I'm not sure if it s= hows exactly the same problem I'm encountering.<br><br>Anyway, this tes= t case is included below in case it is useful.<br><br>On my machine, the te= stcase shows the 3.6 version taking 2 millis and the 3.7 version 11 seconds= .<br><br>/Fredrik Widlert<br><a href=3D"mailto:[email protected]">f= [email protected]</a><br><br><br><br>\set ON_ERROR_STOP on<br>\timin= g on<br><br>/*<br>=C2=A0* Reproducer for slow geometry_columns queries with= the PostGIS 3.7<br>=C2=A0* definition.<br>=C2=A0*<br>=C2=A0* Run this with= psql as a superuser in a new, otherwise empty database.<br>=C2=A0*<br>=C2= =A0* The test deliberately creates many CHECK constraints which have nothin= g to<br>=C2=A0* do with PostGIS.=C2=A0 The 3.7 geometry_columns definition = builds constraint_defs<br>=C2=A0* from every row in pg_constraint, calls pg= _get_constraintdef() for each row,<br>=C2=A0* and scans the result three ti= mes with regular expressions.=C2=A0 Consequently,<br>=C2=A0* unrelated cons= traints affect the time needed to inspect geometry columns.<br>=C2=A0*<br>= =C2=A0* A partitioned table is used to populate pg_constraint without needi= ng<br>=C2=A0* thousands of CREATE TABLE statements: its 100 CHECK constrain= ts are copied<br>=C2=A0* to each of 200 partitions, producing about 20,000 = unrelated<br>=C2=A0* pg_constraint rows.<br>=C2=A0*<br>=C2=A0* The spatial = tables use constraint-based geometry columns rather than<br>=C2=A0* typmods= .=C2=A0 This is important because it exercises the constraint inference<br>= =C2=A0* added to geometry_columns in 3.7.=C2=A0 Views over those tables als= o exercise<br>=C2=A0* _postgis_geometry_columns_view_column_origin().<br>= =C2=A0*<br>=C2=A0* On slower machines, lower the loop upper bounds below.= =C2=A0 The product of the<br>=C2=A0* CHECK-constraint and partition counts = controls most of the catalog load.<br>=C2=A0*/<br><br>CREATE EXTENSION post= gis;<br><br>DO $roles$<br>BEGIN<br> IF NOT EXISTS (<br> SELECT<br> FROM p= g_catalog.pg_roles<br> WHERE rolname =3D 'slow_columns_reader'<br>= ) THEN<br> CREATE ROLE slow_columns_reader NOLOGIN;<br> END IF;<br><br> I= F NOT EXISTS (<br> SELECT<br> FROM pg_catalog.pg_roles<br> WHERE rolname= =3D 'slow_columns_user'<br> ) THEN<br> CREATE ROLE slow_columns_u= ser NOLOGIN;<br> END IF;<br>END<br>$roles$;<br><br>GRANT slow_columns_reade= r TO slow_columns_user;<br><br>CREATE SCHEMA slow_columns_repro;<br><br>/*<= br>=C2=A0* Create the unrelated catalog load first.=C2=A0 Constraints are a= dded to the<br>=C2=A0* parent before its partitions are created so PostgreS= QL copies them into<br>=C2=A0* every partition.<br>=C2=A0*/<br>CREATE TABLE= slow_columns_repro.constraint_noise (<br> id integer NOT NULL<br>) PARTITI= ON BY RANGE (id);<br><br>DO $noise$<br>BEGIN<br> FOR i IN 1..100 LOOP<br> = EXECUTE format(<br> 'ALTER TABLE slow_columns_repro.constraint_noise = '<br> 'ADD CONSTRAINT %I CHECK (id >=3D %s)',<br> 'n= oise_check_' || i,<br> -i<br> );<br> END LOOP;<br><br> FOR i IN 0..1= 99 LOOP<br> EXECUTE format(<br> 'CREATE TABLE slow_columns_repro.%I = '<br> 'PARTITION OF slow_columns_repro.constraint_noise '<br>= 'FOR VALUES FROM (%s) TO (%s)',<br> 'constraint_noise_'= ; || i,<br> i,<br> i + 1<br> );<br> END LOOP;<br>END<br>$noise$;<br><b= r>/*<br>=C2=A0* AddGeometryColumn(..., use_typmod =3D> false) creates le= gacy CHECK<br>=C2=A0* constraints for dimensionality, SRID, and geometry ty= pe.=C2=A0 The example in<br>=C2=A0* slow_columns.sql used typmods and there= fore did not exercise this path.<br>=C2=A0*/<br>DO $spatial_objects$<br>DEC= LARE<br> table_name text;<br> view_name text;<br>BEGIN<br> FOR i IN 0..199 = LOOP<br> table_name :=3D 't' || i;<br> view_name :=3D 'v'= || i;<br><br> EXECUTE format(<br> 'CREATE TABLE slow_columns_repro.= %I (id bigint)',<br> table_name<br> );<br><br> PERFORM AddGeometryC= olumn(<br> 'slow_columns_repro',<br> table_name,<br> 'sha= pe',<br> 5845,<br> 'POINT',<br> 3,<br> false<br> );<br= ><br> EXECUTE format(<br> 'CREATE VIEW slow_columns_repro.%I AS '= ;<br> 'SELECT id, shape FROM slow_columns_repro.%I',<br> view_n= ame,<br> table_name<br> );<br> END LOOP;<br>END<br>$spatial_objects$;<br= ><br>GRANT USAGE ON SCHEMA slow_columns_repro TO slow_columns_reader;<br>GR= ANT SELECT ON ALL TABLES IN SCHEMA slow_columns_repro<br> TO slow_columns_r= eader;<br><br>/*<br>=C2=A0* Preserve the 3.6 definition under another name = so both versions see the<br>=C2=A0* exact same catalog and privileges.<br>= =C2=A0*/<br>CREATE VIEW slow_columns_repro.geometry_columns_36 AS<br>SELECT= <br> current_database()::varchar(256) AS f_table_catalog,<br> n.nspname AS = f_table_schema,<br> c.relname AS f_table_name,<br> a.attname AS f_geometry_= column,<br> COALESCE(postgis_typmod_dims(a.atttypmod), 2) AS coord_dimensio= n,<br> COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), 0) AS srid,<br= > replace(<br> replace(<br> COALESCE(<br> NULLIF(upper(postgis_typmod= _type(a.atttypmod)), 'GEOMETRY'),<br> 'GEOMETRY'<br> )= ,<br> 'ZM',<br> ''<br> ),<br> 'Z',<br> '= '<br> )::varchar(30) AS type<br>FROM pg_catalog.pg_class AS c<br>JOIN p= g_catalog.pg_attribute AS a<br> ON a.attrelid =3D c.oid<br> AND NOT a.attis= dropped<br>JOIN pg_catalog.pg_namespace AS n<br> ON c.relnamespace =3D n.oi= d<br>JOIN pg_catalog.pg_type AS t<br> ON a.atttypid =3D t.oid<br>WHERE c.re= lkind =3D ANY (ARRAY['r'::"char", 'v'::"char= ", 'm'::"char",<br> 'f'::"char", &= #39;p'::"char"])<br> AND c.relname <> 'raster_colum= ns'<br> AND t.typname =3D 'geometry'<br> AND NOT pg_catalog.pg_= is_other_temp_schema(c.relnamespace)<br> AND pg_catalog.has_table_privilege= (c.oid, 'SELECT');<br><br>GRANT SELECT ON slow_columns_repro.geomet= ry_columns_36<br> TO slow_columns_reader;<br><br>/*<br>=C2=A0* Give the pla= nner current catalog statistics.=C2=A0 Without this, results can<br>=C2=A0*= depend on whether autovacuum happened to run during fixture creation.<br>= =C2=A0*/<br>ANALYZE pg_catalog.pg_class;<br>ANALYZE pg_catalog.pg_attribute= ;<br>ANALYZE pg_catalog.pg_constraint;<br>ANALYZE pg_catalog.pg_namespace;<= br>ANALYZE pg_catalog.pg_rewrite;<br>ANALYZE pg_catalog.pg_type;<br><br>SET= ROLE slow_columns_user;<br>SET jit =3D off;<br><br>\echo<br>\echo 'Cat= alog size used by the reproducer:'<br>SELECT count(*) AS constraint_cou= nt<br>FROM pg_catalog.pg_constraint;<br><br>\echo<br>\echo 'PostGIS 3.6= geometry_columns definition (baseline):'<br>EXPLAIN (ANALYZE, BUFFERS,= TIMING OFF, SUMMARY ON)<br>SELECT count(*)<br>FROM slow_columns_repro.geom= etry_columns_36<br>WHERE f_table_schema =3D 'slow_columns_repro';<b= r><br>\echo<br>\echo 'Installed PostGIS geometry_columns definition (3.= 7 regression):'<br>EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)<b= r>SELECT count(*)<br>FROM public.geometry_columns<br>WHERE f_table_schema = =3D 'slow_columns_repro';<br><br>RESET ROLE;<br><br><br><br><br><br= ><br></div> --0000000000007a1b150657d2513f--