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