>Hi > >We are running >postgres server 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)) >POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="120" GEOS="3.9.0-CAPI-1.16.2" >SFCGAL="1.3.7" PROJ="7.2.1" GDAL="GDAL 3.2.1, released 2020/12/29" >LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" >TOPOLOGY RASTER > >The problem is that it takes more than 10 hours (duration: 36885527.039) to >browse tables geometry from qgis https://explain.depesz.com/s/MxAN#bquery with >high load on the server. >We have at least 45 jobs running and around 70% CPU load on the server. > >Then I started to check views/tables involved and found that the view >geometry_columns seems to be using a very long time >'explain analyze select * from geometry_columns' have been waiting for more >than 2 hours now, will paste the result to https://explain.depesz.com when >done. > >While waiting I created temp table for the system tables involved in view >geometry_columns like this > >create temp table pg_attribute_temp as select >attcollation,attrelid,attname,atttypid,attstattarget,attlen,attnum,attndims,attcacheoff,atttypmod,attbyval,attstorage,attalign,attnotnull,atthasdef,atthasmissing,attidentity,attgenerated,attisdropped,attislocal,attinhcount,attacl,attoptions,attfdwoptions > from pg_attribute; >create temp table pg_namespace_temp as select * from pg_namespace; >create temp table pg_type_temp as select * from pg_type; >create temp table pg_constraint_temp as select * from pg_constraint; > >SELECT 1702623 >Time: 42552.899 ms (00:42.553) >SELECT 841 >Time: 132.595 ms >SELECT 245239 >Time: 3378.395 ms (00:03.378) >SELECT 9575 >Time: 205.036 ms > >That did not take very long time. > >Then created geometry_columns_temp_no_rules using those new temp tables. > >explain analyze select * from geometry_columns_temp_no_rules > >And that takes less than 6 seconds with no indexes. Here is the explain from >https://explain.depesz.com/s/yBSd > >Why is temp tables with no indexes much faster system tables with indexes ? > >(I do not think it's related to not having rules I tested to crated a view >using system tables with but with no rules and that hanged for more that 15 >minuttes an dthen I gave up) > >Here is the view def that I used. > >CREATE VIEW geometry_columns_temp_no_rules AS >SELECT current_database()::character varying(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), sn.ndims, 2) AS coord_dimension, > COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), sr.srid, 0) AS srid, > replace(replace(COALESCE(NULLIF(upper(postgis_typmod_type(a.atttypmod)), > 'GEOMETRY'::text), st.type, 'GEOMETRY'::text), 'ZM'::text, ''::text), > 'Z'::text, ''::text)::character varying(30) AS type > FROM pg_class c > JOIN pg_attribute_temp a ON a.attrelid = c.oid AND NOT a.attisdropped > JOIN pg_namespace_temp n ON c.relnamespace = n.oid > JOIN pg_type_temp t ON a.atttypid = t.oid > LEFT JOIN ( SELECT s.connamespace, > s.conrelid, > s.conkey, > replace(split_part(s.consrc, ''''::text, 2), ')'::text, ''::text) > AS type > FROM ( SELECT pg_constraint_temp.connamespace, > pg_constraint_temp.conrelid, > pg_constraint_temp.conkey, > pg_get_constraintdef(pg_constraint_temp.oid) AS consrc > FROM pg_constraint_temp) s > WHERE s.consrc ~~* '%geometrytype(% = %'::text) st ON > st.connamespace = n.oid AND st.conrelid = c.oid AND (a.attnum = ANY > (st.conkey)) > LEFT JOIN ( SELECT s.connamespace, > s.conrelid, > s.conkey, > replace(split_part(s.consrc, ' = '::text, 2), ')'::text, > ''::text)::integer AS ndims > FROM ( SELECT pg_constraint_temp.connamespace, > pg_constraint_temp.conrelid, > pg_constraint_temp.conkey, > pg_get_constraintdef(pg_constraint_temp.oid) AS consrc > FROM pg_constraint_temp) s > WHERE s.consrc ~~* '%ndims(% = %'::text) sn ON sn.connamespace = > n.oid AND sn.conrelid = c.oid AND (a.attnum = ANY (sn.conkey)) > LEFT JOIN ( SELECT s.connamespace, > s.conrelid, > s.conkey, > replace(replace(split_part(s.consrc, ' = '::text, 2), ')'::text, > ''::text), '('::text, ''::text)::integer AS srid > FROM ( SELECT pg_constraint_temp.connamespace, > pg_constraint_temp.conrelid, > pg_constraint_temp.conkey, > pg_get_constraintdef(pg_constraint_temp.oid) AS consrc > FROM pg_constraint_temp) s > WHERE s.consrc ~~* '%srid(% = %'::text) sr ON sr.connamespace = > n.oid AND sr.conrelid = c.oid AND (a.attnum = ANY (sr.conkey)) > WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", > 'f'::"char", 'p'::"char"])) AND NOT c.relname = 'raster_columns'::name AND > t.typname = 'geometry'::name AND NOT pg_is_other_temp_schema(c.relnamespace) > AND has_table_privilege(c.oid, 'SELECT'::text); >; > >Thanks. > >Lars Hi
I did another test. Here ( https://explain.depesz.com/s/H7f9 ) I use pg_attribute_temp and we use around 6 seconds But in this query (https://explain.depesz.com/s/Op7i) I use pg_attribute system table directly and execution time is around 50 seconds The explain analyze is still running on select * from geometry_columns. Thanks Lars
