RE: Slower queries on geometry_columns on 3.7.0beta1
"Regina Obe" <[email protected]> Tue, 4 Aug 2026 10:21:08 -0400
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <[email protected]> |
This is a multipart message in MIME format.
------=_NextPart_000_0005_01DD23FA.F768CE60
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Strange and you don=E2=80=99t even have that many tables.
=20
Can you absolutely confirm the view is right in the database that is =
still slow
=20
\d+ geometry_columns
=20
Is that 30 secs speed the not fixed one or the recreate in a test? =
30secs seems way better than the 8 minutes I thought you had mentioned =
before, though still much slower than it should be for a 100 geometry =
columns database. =20
=20
Can you try running the first part of the view again and give us the =
time it takes to run on both systems.
=20
SELECT connamespace,=20
conrelid,=20
conkey,=20
pg_catalog.regexp_replace(pg_catalog.pg_get_constraintdef(oid), =
$$\s+NOT\s+VALID$$, '', 'i') AS consrc=20
FROM pg_catalog.pg_constraint
=20
=20
I think we can probably tighten up the constraint query a bit so =
I=E2=80=99ll work on that.
=20
I find this part very troubling as it seems to be checking all the =
constraints (not just geometry ones).
=20
Nested Loop =
<https://www.depesz.com/2013/05/09/explaining-the-unexplainable-part-3/#n=
ested-loop> (cost=3D8.59..692.30 rows=3D1 width=3D7) (actual =
time=3D5.673..6.476 rows=3D109.00 loops=3D1)=20
* Join Filter: ((c.relnamespace =3D n.oid) AND CASE WHEN =
has_schema_privilege(c.relnamespace, 'USAGE'::text) THEN CASE WHEN =
((to_regclass(format('%I.%I'::text, n.nspname, c.relname)))::oid =3D =
c.oid) THEN has_column_privilege(c.oid, (a.attname)::text, =
'SELECT'::text) ELSE false END ELSE has_column_privilege(c.oid, =
(a.attname)::text, 'SELECT'::text) END)
* Rows Removed by Join Filter: 5,995
* Buffers: shared hit=3D723
=20
=20
From: Fredrik Widlert <[email protected]>=20
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
=20
Hi, thanks for looking into it!
=20
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).
=20
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=20
-------
473
(1 row)
dps=3D# select count(*) from pg_constraint;
count=20
-------
3160
(1 row)
dps=3D# \timing
Timing is on.
dps=3D# select count(*) from geometry_columns ;
count=20
-------
109
(1 row)
Time: 30722.082 ms (00:30.722)
dps=3D#=20
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=20
-------
2524
(1 row)
=20
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.
=20
Is there any other information I can provide to help diagnose the =
problem?
=20
/Fredrik
=20
=20
=20
On Sat, Aug 1, 2026 at 4:25=E2=80=AFAM Paragon Corporation <[email protected] =
<mailto:[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/9f52dd2f9eeb30f7bc5f774eefe28002=
0f320c52/git
On Thu, Jul 30, 2026 at 7:24=E2=80=AFAM Fredrik Widlert
<[email protected] <mailto:[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] <mailto:[email protected]>=20
>
>
>
> \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 =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 =
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 >=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 =
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 :=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;
>
>
>
>
>
>
------=_NextPart_000_0005_01DD23FA.F768CE60
Content-Type: text/html;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><meta =
http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8"><meta =
name=3DGenerator content=3D"Microsoft Word 15 (filtered =
medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:12.0pt;
font-family:"Aptos",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Aptos",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:1340349532;
mso-list-template-ids:1564084646;}
@list l0:level1
{mso-level-number-format:bullet;
mso-level-text:=EF=82=B7;
mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:"Courier New";
mso-bidi-font-family:"Times New Roman";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:=EF=82=A7;
mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:=EF=82=A7;
mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:=EF=82=A7;
mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:=EF=82=A7;
mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:=EF=82=A7;
mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:=EF=82=A7;
mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:=EF=82=A7;
mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body lang=3DEN-US link=3Dblue =
vlink=3Dpurple style=3D'word-wrap:break-word'><div =
class=3DWordSection1><p class=3DMsoNormal>Strange and you don=E2=80=99t =
even have that many tables.<o:p></o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>Can you =
absolutely confirm the view is right in the database that is still =
slow<o:p></o:p></p><p class=3DMsoNormal><o:p> </o:p></p><p =
class=3DMsoNormal>\d+ geometry_columns<o:p></o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>Is that 30 =
secs speed the not fixed one or the recreate in a test?=C2=A0 30secs =
seems way better than the 8 minutes I thought you had mentioned before, =
though still much slower than it should be for a 100 geometry columns =
database.=C2=A0 <o:p></o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>Can you try =
running the first part of the view again and give us the time it takes =
to run on both systems.<o:p></o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><table class=3DMsoNormalTable =
border=3D0 cellpadding=3D0><tr><td style=3D'padding:.75pt .75pt .75pt =
.75pt'><p class=3DMsoNormal>SELECT connamespace, =
<o:p></o:p></p></td></tr><tr><td style=3D'padding:.75pt .75pt .75pt =
.75pt'><p class=3DMsoNormal>conrelid, <o:p></o:p></p></td></tr><tr><td =
style=3D'padding:.75pt .75pt .75pt .75pt'><p class=3DMsoNormal>conkey, =
<o:p></o:p></p></td></tr><tr><td style=3D'padding:.75pt .75pt .75pt =
.75pt'><p =
class=3DMsoNormal>pg_catalog.regexp_replace(pg_catalog.pg_get_constraintd=
ef(oid), $$\s+NOT\s+VALID$$, '', 'i') AS consrc =
<o:p></o:p></p></td></tr><tr><td style=3D'padding:.75pt .75pt .75pt =
.75pt'><p class=3DMsoNormal>FROM =
pg_catalog.pg_constraint<o:p></o:p></p></td></tr></table><p =
class=3DMsoNormal><o:p> </o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>I think we =
can probably tighten up the constraint query a bit so I=E2=80=99ll work =
on that.<o:p></o:p></p><p class=3DMsoNormal><o:p> </o:p></p><p =
class=3DMsoNormal>I find this part very troubling as it seems to be =
checking all the constraints (not just geometry ones).<o:p></o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal><a =
href=3D"https://www.depesz.com/2013/05/09/explaining-the-unexplainable-pa=
rt-3/#nested-loop">Nested Loop</a> (cost=3D8.59..692.30 rows=3D1 =
width=3D7) (actual time=3D5.673..6.476 rows=3D109.00 loops=3D1) =
<o:p></o:p></p><ul style=3D'margin-top:0in' type=3Ddisc><li =
class=3DMsoNormal style=3D'mso-list:l0 level1 lfo1'>Join Filter: =
((c.relnamespace =3D n.oid) AND CASE WHEN =
has_schema_privilege(c.relnamespace, 'USAGE'::text) THEN CASE WHEN =
((to_regclass(format('%I.%I'::text, n.nspname, c.relname)))::oid =3D =
c.oid) THEN has_column_privilege(c.oid, (a.attname)::text, =
'SELECT'::text) ELSE false END ELSE has_column_privilege(c.oid, =
(a.attname)::text, 'SELECT'::text) END)<o:p></o:p></li><li =
class=3DMsoNormal style=3D'mso-list:l0 level1 lfo1'>Rows Removed by Join =
Filter: <b>5,995</b><o:p></o:p></li><li class=3DMsoNormal =
style=3D'mso-list:l0 level1 lfo1'>Buffers: shared =
hit=3D723<o:p></o:p></li></ul><p =
class=3DMsoNormal><o:p> </o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><div =
style=3D'border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in =
4.0pt'><div><div style=3D'border:none;border-top:solid #E1E1E1 =
1.0pt;padding:3.0pt 0in 0in 0in'><p class=3DMsoNormal><b><span =
style=3D'font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span><=
/b><span style=3D'font-size:11.0pt;font-family:"Calibri",sans-serif'> =
Fredrik Widlert <[email protected]> <br><b>Sent:</b> =
Tuesday, August 4, 2026 5:30 AM<br><b>To:</b> Paragon Corporation =
<[email protected]><br><b>Cc:</b> =
[email protected]<br><b>Subject:</b> Re: Slower queries on =
geometry_columns on 3.7.0beta1<o:p></o:p></span></p></div></div><p =
class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>Hi, thanks =
for looking into it!<o:p></o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>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).<o:p></o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>In a test =
database where I've installed some of my real schemas, it looks like =
this:<o:p></o:p></p><p class=3DMsoNormal =
style=3D'margin-bottom:12.0pt'>dps=3D# select count(*) from =
pg_tables;<br> count <br>-------<br> 473<br>(1 =
row)<br><br>dps=3D# select count(*) from pg_constraint;<br> count =
<br>-------<br> 3160<br>(1 row)<br><br>dps=3D# \timing<br>Timing =
is on.<br>dps=3D# select count(*) from geometry_columns ;<br> count =
<br>-------<br> 109<br>(1 row)<br><br>Time: 30722.082 ms =
(00:30.722)<br>dps=3D# <br>dps=3D# explain (analyze, buffers) select =
count(*) from geometry_columns ;<o:p></o:p></p><p class=3DMsoNormal =
style=3D'margin-bottom:12.0pt'>The result of the explain is here:<br><a =
href=3D"https://explain.depesz.com/s/e2q2">https://explain.depesz.com/s/e=
2q2</a><o:p></o:p></p><p class=3DMsoNormal>I think most of my =
constraints are just "not null" constraints unrelated to =
geometries:<o:p></o:p></p><p class=3DMsoNormal>dps=3D# select count(*) =
from pg_constraint where conname like '%not_null%';<br> count =
<br>-------<br> 2524<br>(1 row)<o:p></o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>Since the =
view in 3.7beta1 includes operations on ACLs and is much more =
complex than before,<o:p></o:p></p><p class=3DMsoNormal>I guess it's not =
very surprising that it has become slower, but something still seems =
wrong here.<o:p></o:p></p><p class=3DMsoNormal><o:p> </o:p></p><p =
class=3DMsoNormal>Is there any other information I can provide to help =
diagnose the problem?<o:p></o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><p =
class=3DMsoNormal>/Fredrik<o:p></o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><p =
class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>On Sat, Aug =
1, 2026 at 4:25<span =
style=3D'font-family:"Arial",sans-serif'>=E2=80=AF</span>AM Paragon =
Corporation <<a href=3D"mailto:[email protected]">[email protected]</a>> =
wrote:<o:p></o:p></p><blockquote style=3D'border:none;border-left:solid =
#CCCCCC 1.0pt;padding:0in 0in 0in =
6.0pt;margin-left:4.8pt;margin-right:0in'><p class=3DMsoNormal>I have =
the issue ticketed here -<br><a =
href=3D"https://trac.osgeo.org/postgis/ticket/6110" =
target=3D"_blank">https://trac.osgeo.org/postgis/ticket/6110</a> =
though 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 top.<br><br>As detailed here --<br><a =
href=3D"https://trac.osgeo.org/postgis/changeset/9f52dd2f9eeb30f7bc5f774e=
efe280020f320c52/git" =
target=3D"_blank">https://trac.osgeo.org/postgis/changeset/9f52dd2f9eeb30=
f7bc5f774eefe280020f320c52/git</a><br><br>On Thu, Jul 30, 2026 at =
7:24<span style=3D'font-family:"Arial",sans-serif'>=E2=80=AF</span>AM =
Fredrik Widlert<br><<a href=3D"mailto:[email protected]" =
target=3D"_blank">[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 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" =
target=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 =
reproduce something similar<br>> in an empty database, but I'm not =
sure if it shows exactly the same 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">[email protected]</a><br>><br>><br>><b=
r>> \set ON_ERROR_STOP on<br>> \timing on<br>><br>> =
/*<br>> * Reproducer for slow geometry_columns queries with the =
PostGIS 3.7<br>> * definition.<br>> *<br>> * =
Run this with psql as a superuser in a new, otherwise empty =
database.<br>> *<br>> * The test deliberately creates =
many CHECK constraints which have nothing to<br>> * do with =
PostGIS. The 3.7 geometry_columns definition builds =
constraint_defs<br>> * from every row in pg_constraint, calls =
pg_get_constraintdef() for each row,<br>> * and scans the =
result three times with regular expressions. =
Consequently,<br>> * unrelated constraints affect the time =
needed to inspect geometry columns.<br>> *<br>> * A =
partitioned table is used to populate pg_constraint without =
needing<br>> * thousands of CREATE TABLE statements: its 100 =
CHECK constraints are copied<br>> * to each of 200 partitions, =
producing about 20,000 unrelated<br>> * pg_constraint =
rows.<br>> *<br>> * The spatial tables use =
constraint-based geometry columns rather than<br>> * =
typmods. This is important because it exercises the constraint =
inference<br>> * added to geometry_columns in 3.7. Views =
over those tables also exercise<br>> * =
_postgis_geometry_columns_view_column_origin().<br>> =
*<br>> * On slower machines, lower the loop upper bounds =
below. The product of the<br>> * CHECK-constraint and =
partition counts controls most of the catalog load.<br>> =
*/<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>> * Create the =
unrelated catalog load first. Constraints are added to =
the<br>> * parent before its partitions are created so =
PostgreSQL copies them into<br>> * every =
partition.<br>> */<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>> * AddGeometryColumn(..., =
use_typmod =3D> false) creates legacy CHECK<br>> * =
constraints for dimensionality, SRID, and geometry type. The =
example in<br>> * slow_columns.sql used typmods and therefore =
did not exercise this path.<br>> */<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>> * Preserve the 3.6 =
definition under another name so both versions see the<br>> * =
exact same catalog and privileges.<br>> */<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'),<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 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'::"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>> * Give the planner =
current catalog statistics. Without this, results =
can<br>> * depend on whether autovacuum happened to run during =
fixture creation.<br>> */<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 regression):'<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>><o:p></o:p></p></blo=
ckquote></div></div></body></html>
------=_NextPart_000_0005_01DD23FA.F768CE60--