Re: Slower queries on geometry_columns on 3.7.0beta1
Fredrik Widlert <[email protected]> Tue, 4 Aug 2026 11:30:01 +0200
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <CADfhSr9PX1BNtu4iAWtLyzE+PYpEs1H_KH_JJYBDKePxZN3tag@mail.gmail.com> |
--00000000000039ec1a0658354d6a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=3D# select count(*) from pg_tables; count ------- 473 (1 row) dps=3D# select count(*) from pg_constraint; count ------- 3160 (1 row) dps=3D# \timing Timing is on. dps=3D# select count(*) from geometry_columns ; count ------- 109 (1 row) Time: 30722.082 ms (00:30.722) dps=3D# dps=3D# 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=3D# 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=E2=80=AFAM Paragon Corporation <[email protected]> wr= ote: > 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/9f52dd2f9eeb30f7bc5f774eefe28002= 0f320c52/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 m= y > 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 needin= g > > * 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 =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 t= he > > * 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 >=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 exampl= e > 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 > 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 =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 > 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 =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; > > > > > > > > > > > > > --00000000000039ec1a0658354d6a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr"><div>Hi, thanks for looking into it!</div><div><br></div><= div>I've tried running a "create or replace view" with the ne= w version containing=C2=A0AS NOT MATERIALIZED, but it does not seem to fix = the problem in my test database (although it does make the query significan= tly faster when I test with the script to reproduce the problem in an empty= database).</div><div><br></div><div>In a test database where I've inst= alled some of my real schemas, it looks like this:</div><div>dps=3D# select= count(*) from pg_tables;<br>=C2=A0count <br>-------<br>=C2=A0 =C2=A0473<br= >(1 row)<br><br>dps=3D# select count(*) from pg_constraint;<br>=C2=A0count = <br>-------<br>=C2=A0 3160<br>(1 row)<br><br>dps=3D# \timing<br>Timing is o= n.<br>dps=3D# select count(*) from geometry_columns ;<br>=C2=A0count <br>--= -----<br>=C2=A0 =C2=A0109<br>(1 row)<br><br>Time: 30722.082 ms (00:30.722)<= br>dps=3D# <br>dps=3D# explain (analyze, buffers) select count(*) from geom= etry_columns ;<br><br></div><div>The result of the explain is here:<br><a h= ref=3D"https://explain.depesz.com/s/e2q2">https://explain.depesz.com/s/e2q2= </a><br><br></div><div>I think most of my constraints are just "not nu= ll" constraints unrelated to geometries:</div><div>dps=3D# select coun= t(*) from pg_constraint where conname like '%not_null%';<br>=C2=A0c= ount <br>-------<br>=C2=A0 2524<br>(1 row)</div><div><br></div><div>Since t= he view=C2=A0in 3.7beta1=C2=A0includes operations on ACLs and is much more = complex than before,</div><div>I guess it's not very surprising that it= has become slower, but something still seems wrong here.</div><div><br></d= iv><div>Is there any other information I can provide to help diagnose the p= roblem?</div><div><br></div><div>/Fredrik</div><div><br></div><div><br></di= v></div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"lt= r" class=3D"gmail_attr">On Sat, Aug 1, 2026 at 4:25=E2=80=AFAM Paragon Corp= oration <<a href=3D"mailto:[email protected]">[email protected]</a>> wrote:<br></= div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor= der-left:1px solid rgb(204,204,204);padding-left:1ex">I have the issue tick= eted here -<br> <a href=3D"https://trac.osgeo.org/postgis/ticket/6110" rel=3D"noreferrer" t= arget=3D"_blank">https://trac.osgeo.org/postgis/ticket/6110</a>=C2=A0 thoug= h I think it might<br> only affect constraint based tables.<br> <br> Can you try this change, just add a AS NOT MATERIALIZED to the CTE at the t= op.<br> <br> As detailed here --<br> <a href=3D"https://trac.osgeo.org/postgis/changeset/9f52dd2f9eeb30f7bc5f774= eefe280020f320c52/git" rel=3D"noreferrer" target=3D"_blank">https://trac.os= geo.org/postgis/changeset/9f52dd2f9eeb30f7bc5f774eefe280020f320c52/git</a><= br> <br> On Thu, Jul 30, 2026 at 7:24=E2=80=AFAM Fredrik Widlert<br> <<a href=3D"mailto:[email protected]" target=3D"_blank">fredrik.= [email protected]</a>> wrote:<br> ><br> > Hi, I tested postgis-3.7.0beta1 today on PostgreSQL 19 beta 2.<br> ><br> > It appears that selects on geometry_columns has become much slower 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 wha= t it was in earlier versions,<br> > so I guess the change is related to the new PostGIS rather than the ne= w PostgreSQL.<br> ><br> > Query plan for the new version here:<br> > <a href=3D"https://explain.depesz.com/s/rtSg" rel=3D"noreferrer" targe= t=3D"_blank">https://explain.depesz.com/s/rtSg</a><br> ><br> > Is this a known problem? I had Codex build a test case that seems to r= eproduce something similar<br> > in an empty database, but I'm not sure if it shows exactly the sam= e problem I'm encountering.<br> ><br> > Anyway, this test case is included below in case it is useful.<br> ><br> > On my machine, the testcase 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]" target=3D"_blank">fredrik= [email protected]</a><br> ><br> ><br> ><br> > \set ON_ERROR_STOP on<br> > \timing 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 da= tabase.<br> >=C2=A0 *<br> >=C2=A0 * The test deliberately creates many CHECK constraints which hav= e nothing to<br> >=C2=A0 * do with PostGIS.=C2=A0 The 3.7 geometry_columns definition bui= lds 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 times with regular expressions.=C2= =A0 Consequently,<br> >=C2=A0 * unrelated constraints affect the time needed to inspect geomet= ry columns.<br> >=C2=A0 *<br> >=C2=A0 * A partitioned table is used to populate pg_constraint without = needing<br> >=C2=A0 * thousands of CREATE TABLE statements: its 100 CHECK constraint= s 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 rathe= r than<br> >=C2=A0 * typmods.=C2=A0 This is important because it exercises the cons= traint inference<br> >=C2=A0 * added to geometry_columns in 3.7.=C2=A0 Views over those table= s also 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 T= he product of the<br> >=C2=A0 * CHECK-constraint and partition counts controls most of the cat= alog load.<br> >=C2=A0 */<br> ><br> > CREATE EXTENSION postgis;<br> ><br> > DO $roles$<br> > BEGIN<br> > IF NOT EXISTS (<br> > SELECT<br> > FROM pg_catalog.pg_roles<br> > WHERE rolname =3D 'slow_columns_reader'<br> > ) THEN<br> > CREATE ROLE slow_columns_reader NOLOGIN;<br> > END IF;<br> ><br> > IF NOT EXISTS (<br> > SELECT<br> > FROM pg_catalog.pg_roles<br> > WHERE rolname =3D 'slow_columns_user'<br> > ) THEN<br> > CREATE ROLE slow_columns_user NOLOGIN;<br> > END IF;<br> > END<br> > $roles$;<br> ><br> > GRANT slow_columns_reader 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= added to the<br> >=C2=A0 * parent before its partitions are created so PostgreSQL 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> > ) PARTITION 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> > 'noise_check_' || i,<br> > -i<br> > );<br> > END LOOP;<br> ><br> > FOR i IN 0..199 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> ><br> > /*<br> >=C2=A0 * AddGeometryColumn(..., use_typmod =3D> false) creates legac= y CHECK<br> >=C2=A0 * constraints for dimensionality, SRID, and geometry type.=C2=A0= The example in<br> >=C2=A0 * slow_columns.sql used typmods and therefore did not exercise t= his path.<br> >=C2=A0 */<br> > DO $spatial_objects$<br> > DECLARE<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 AddGeometryColumn(<br> > 'slow_columns_repro',<br> > table_name,<br> > 'shape',<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_name,<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> > GRANT SELECT ON ALL TABLES IN SCHEMA slow_columns_repro<br> > TO slow_columns_reader;<br> ><br> > /*<br> >=C2=A0 * Preserve the 3.6 definition under another name so both version= s 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_dimension,<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'),<b= r> > 'GEOMETRY'<br> > ),<br> > 'ZM',<br> > ''<br> > ),<br> > 'Z',<br> > ''<br> > )::varchar(30) AS type<br> > FROM pg_catalog.pg_class AS c<br> > JOIN pg_catalog.pg_attribute AS a<br> > ON a.attrelid =3D c.oid<br> > AND NOT a.attisdropped<br> > JOIN pg_catalog.pg_namespace AS n<br> > ON c.relnamespace =3D n.oid<br> > JOIN pg_catalog.pg_type AS t<br> > ON a.atttypid =3D t.oid<br> > WHERE c.relkind =3D ANY (ARRAY['r'::"char", 'v&#= 39;::"char", 'm'::"char",<br> > 'f'::"char", 'p'::"char"])<br> > AND c.relname <> 'raster_columns'<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.geometry_columns_36<br> > TO slow_columns_reader;<br> ><br> > /*<br> >=C2=A0 * Give the planner current catalog statistics.=C2=A0 Without thi= s, results can<br> >=C2=A0 * depend on whether autovacuum happened to run during fixture cr= eation.<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 'Catalog size used by the reproducer:'<br> > SELECT count(*) AS constraint_count<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.geometry_columns_36<br> > WHERE f_table_schema =3D 'slow_columns_repro';<br> ><br> > \echo<br> > \echo 'Installed PostGIS geometry_columns definition (3.7 regressi= on):'<br> > EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)<br> > 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> </blockquote></div> --00000000000039ec1a0658354d6a--