|
>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
|
