RE: Slower queries on geometry_columns on 3.7.0beta1
"Regina Obe" <[email protected]> Tue, 4 Aug 2026 14:43:08 -0400
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <[email protected]> |
This is a multipart message in MIME format. ------=_NextPart_000_0001_01DD241F.91DED970 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0002_01DD241F.91DED970" ------=_NextPart_001_0002_01DD241F.91DED970 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Fredrik, =20 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=E2=80=99ve backed out that feature as discussed here - = https://trac.osgeo.org/postgis/ticket/1705 =20 Can you give this version a try. =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_001_0002_01DD241F.91DED970 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:"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;} --></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>Fredrik,<o:p></o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>I think the = remaining culprit was the feature addition to recurse views to get the = base geometry type.=C2=A0 After I took that out my tests took = significantly less time.<o:p></o:p></p><p class=3DMsoNormal>I=E2=80=99ve = backed out that feature as discussed here - <a = href=3D"https://trac.osgeo.org/postgis/ticket/1705">https://trac.osgeo.or= g/postgis/ticket/1705</a><o:p></o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>Can you give = this version a try.<o:p></o:p></p><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_001_0002_01DD241F.91DED970-- ------=_NextPart_000_0001_01DD241F.91DED970 Content-Type: application/octet-stream; name="geometry_columns.sql" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="geometry_columns.sql" CREATE OR REPLACE VIEW geometry_columns AS=0A= WITH constraint_defs AS NOT MATERIALIZED (=0A= -- Trim trailing NOT VALID so metadata inference works before = constraint validation (#4828).=0A= SELECT connamespace,=0A= conrelid,=0A= conkey,=0A= = pg_catalog.regexp_replace(pg_catalog.pg_get_constraintdef(oid), = $$\s+NOT\s+VALID$$, '', 'i') AS consrc=0A= FROM pg_catalog.pg_constraint=0A= )=0A= SELECT pg_catalog.current_database()::pg_catalog.varchar(256) AS = f_table_catalog,=0A= n.nspname AS f_table_schema,=0A= c.relname AS f_table_name,=0A= a.attname AS f_geometry_column,=0A= COALESCE(postgis_typmod_dims(a.atttypmod), sn.ndims, 2) AS = coord_dimension,=0A= COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), sr.srid, 0) AS = srid,=0A= = pg_catalog.replace(pg_catalog.replace(COALESCE(NULLIF(pg_catalog.upper(po= stgis_typmod_type(a.atttypmod)), 'GEOMETRY'::pg_catalog.text), st.type, = 'GEOMETRY'::pg_catalog.text), 'ZM'::pg_catalog.text, = ''::pg_catalog.text), 'Z'::pg_catalog.text, = ''::pg_catalog.text)::pg_catalog.varchar(30) AS type=0A= FROM pg_catalog.pg_class c=0A= JOIN pg_catalog.pg_attribute a ON a.attrelid = OPERATOR(pg_catalog.=3D) c.oid AND NOT a.attisdropped=0A= JOIN pg_catalog.pg_namespace n ON c.relnamespace = OPERATOR(pg_catalog.=3D) n.oid=0A= JOIN pg_catalog.pg_type t ON a.atttypid OPERATOR(pg_catalog.=3D) = t.oid=0A= LEFT JOIN (=0A= SELECT s.connamespace,=0A= s.conrelid,=0A= s.conkey,=0A= (pg_catalog.regexp_match(s.consrc, = $$geometrytype\(\w+\)\s*=3D\s*'(\w+)'$$, 'i'))[1]::pg_catalog.text AS = type=0A= FROM constraint_defs AS s=0A= WHERE s.consrc OPERATOR(pg_catalog.~*) = $$geometrytype\(\w+\)\s*=3D\s*'\w+'$$::pg_catalog.text=0A= ) st ON st.conrelid OPERATOR(pg_catalog.=3D) c.oid AND (a.attnum = OPERATOR(pg_catalog.=3D) ANY (st.conkey))=0A= LEFT JOIN (=0A= SELECT s.connamespace,=0A= s.conrelid,=0A= s.conkey,=0A= (pg_catalog.regexp_match(s.consrc, = $$ndims\(\w+\)\s*=3D\s*(\d+)$$, 'i'))[1]::integer AS ndims=0A= FROM constraint_defs AS s=0A= WHERE s.consrc OPERATOR(pg_catalog.~*) = $$ndims\(\w+\)\s*=3D\s*\d+$$::pg_catalog.text=0A= ) sn ON sn.conrelid OPERATOR(pg_catalog.=3D) c.oid AND (a.attnum = OPERATOR(pg_catalog.=3D) ANY (sn.conkey))=0A= LEFT JOIN (=0A= SELECT s.connamespace,=0A= s.conrelid,=0A= s.conkey,=0A= (pg_catalog.regexp_match(s.consrc, = $$srid\(\w+\)\s*=3D\s*(\d+)$$, 'i'))[1]::integer As srid=0A= FROM constraint_defs AS s=0A= WHERE s.consrc OPERATOR(pg_catalog.~*) = $$srid\(\w+\)\s*=3D\s*\d+$$::pg_catalog.text=0A= ) sr ON sr.conrelid OPERATOR(pg_catalog.=3D) c.oid AND (a.attnum = OPERATOR(pg_catalog.=3D) ANY (sr.conkey))=0A= WHERE (c.relkind OPERATOR(pg_catalog.=3D) ANY = (ARRAY['r'::pg_catalog."char", 'v'::pg_catalog."char", = 'm'::pg_catalog."char", 'f'::pg_catalog."char", 'p'::pg_catalog."char"]))=0A= AND NOT c.relname OPERATOR(pg_catalog.=3D) = 'raster_columns'::pg_catalog.name AND t.typname OPERATOR(pg_catalog.=3D) = 'geometry'::pg_catalog.name=0A= AND NOT pg_catalog.pg_is_other_temp_schema(c.relnamespace)=0A= AND CASE WHEN pg_catalog.has_schema_privilege(c.relnamespace, = 'USAGE'::pg_catalog.text)=0A= THEN CASE WHEN = pg_catalog.to_regclass(pg_catalog.format('%I.%I', n.nspname, c.relname)) = OPERATOR(pg_catalog.=3D) c.oid=0A= THEN pg_catalog.has_column_privilege(c.oid, = a.attname, 'SELECT'::pg_catalog.text)=0A= ELSE false=0A= END=0A= ELSE pg_catalog.has_column_privilege(c.oid, a.attname, = 'SELECT'::pg_catalog.text)=0A= END=0A= AND (=0A= EXISTS (=0A= SELECT 1=0A= FROM pg_catalog.pg_roles=0A= WHERE rolname OPERATOR(pg_catalog.=3D) current_user=0A= AND rolsuper=0A= )=0A= OR EXISTS (=0A= SELECT 1=0A= FROM pg_catalog.pg_roles=0A= WHERE rolname OPERATOR(pg_catalog.=3D) 'pg_read_all_data'=0A= AND pg_catalog.pg_has_role(current_user, oid, 'USAGE')=0A= )=0A= OR EXISTS (=0A= SELECT 1=0A= FROM pg_catalog.aclexplode(COALESCE(c.relacl, = pg_catalog.acldefault('r', c.relowner))) AS acl=0A= WHERE acl.privilege_type OPERATOR(pg_catalog.=3D) 'SELECT'=0A= AND (acl.grantee OPERATOR(pg_catalog.=3D) 0 OR = pg_catalog.pg_has_role(acl.grantee, 'USAGE'))=0A= )=0A= OR EXISTS (=0A= SELECT 1=0A= FROM pg_catalog.aclexplode(a.attacl) AS acl=0A= WHERE acl.privilege_type OPERATOR(pg_catalog.=3D) 'SELECT'=0A= AND (acl.grantee OPERATOR(pg_catalog.=3D) 0 OR = pg_catalog.pg_has_role(acl.grantee, 'USAGE'))=0A= )=0A= );=0A= ------=_NextPart_000_0001_01DD241F.91DED970--