On 01/11/2012 07:00 PM, Scott Marlowe wrote:
This is a problem I've run into before, but I can't find the previous
post on it. When you run a \d command, if you run top on your server
do you see a single CPU spinning hard on that one command? If so then
it's a pg server side problem, which is what I had on one server with
~40k objects in it.
Off the top of my head I remember something like this helping:
alter function pg_table_is_visible cost 10;
Thanks for the feedback. I found the relevant parts in the qgis source
code and have been able to trace the problem. It's just a sub-optimal
query issued by qgis:
SELECT
pg_class.relname,pg_namespace.nspname,pg_attribute.attname,pg_class.relkind
FROM pg_attribute,pg_class,pg_namespace
WHERE pg_namespace.oid=pg_class.relnamespace AND pg_attribute.attrelid =
pg_class.oid
AND ( EXISTS (SELECT * FROM pg_type WHERE
pg_type.oid=pg_attribute.atttypid AND pg_type.typname IN
('geometry','geography','topogeometry')) OR pg_attribute.atttypid IN
(SELECT oid FROM pg_type a WHERE EXISTS (SELECT * FROM pg_type b WHERE
a.typbasetype=b.oid AND b.typname IN
('geometry','geography','topogeometry'))))
AND has_schema_privilege( pg_namespace.nspname, 'usage' )
AND has_table_privilege( '"' || pg_namespace.nspname || '"."' ||
pg_class.relname || '"', 'select' )
AND NOT EXISTS (SELECT * FROM geometry_columns WHERE
pg_namespace.nspname=f_table_schema AND pg_class.relname=f_table_name)
AND pg_class.relkind IN ('v','r');
When leaving out the last two "AND NOT EXISTS..." parts, the query
finishes in no time. I have attached the output of EXPLAIN ANALYZE -- if
I understand the execution tree correctly, the time is burnt in repeated
sequential scans of the geometry_columns table (line 38).? Rewriting the
"AND NOT EXISTS" part using WITH solves the performance issues here, but
works only from Postgres 8.4. Any idea how to speed up this query for
older versions? (Creating a temporary table or an index should be avoided.)
Kirill
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------
Nested Loop (cost=33350.33..40392.79 rows=13 width=193) (actual time=1073.299..171226.309 rows=4821 loops=1)
-> Nested Loop Anti Join (cost=30586.95..37463.23 rows=1 width=133) (actual time=958.850..170675.643 rows=9343 loops=1)
Join Filter: ((pg_namespace.nspname = (geometry_columns.f_table_schema)::name) AND (pg_class.relname = (geometry_columns.f_table_name)::name))
-> Hash Anti Join (cost=30586.95..37075.41 rows=1 width=133) (actual time=945.911..1760.307 rows=17836 loops=1)
Hash Cond: ((pg_namespace.nspname = n.nspname) AND (pg_class.relname = c.relname))
-> Hash Join (cost=7.64..5076.89 rows=2426 width=133) (actual time=1.264..769.633 rows=17836 loops=1)
Hash Cond: (pg_class.relnamespace = pg_namespace.oid)
Join Filter: has_table_privilege((((('"'::text || (pg_namespace.nspname)::text) || '"."'::text) || (pg_class.relname)::text) || '"'::text
), 'select'::text)
-> Seq Scan on pg_class (cost=0.00..4802.82 rows=17676 width=73) (actual time=0.039..135.094 rows=17850 loops=1)
Filter: (relkind = ANY ('{v,r}'::"char"[]))
-> Hash (cost=6.85..6.85 rows=63 width=68) (actual time=1.172..1.172 rows=157 loops=1)
-> Seq Scan on pg_namespace (cost=0.00..6.85 rows=63 width=68) (actual time=0.027..0.938 rows=157 loops=1)
Filter: has_schema_privilege((nspname)::text, 'usage'::text)
-> Hash (cost=29919.24..29919.24 rows=44005 width=128) (actual time=944.583..944.583 rows=0 loops=1)
-> Hash Join (cost=5294.84..29919.24 rows=44005 width=128) (actual time=944.582..944.582 rows=0 loops=1)
Hash Cond: (a.attrelid = c.oid)
-> Hash Join (cost=19.75..22908.77 rows=94205 width=4) (actual time=944.580..944.580 rows=0 loops=1)
Hash Cond: (a.atttypid = t.oid)
-> Seq Scan on pg_attribute a (cost=0.00..19562.42 rows=635881 width=8) (actual time=0.007..582.303 rows=647146 loops=1)
Filter: (NOT attisdropped)
-> Hash (cost=19.70..19.70 rows=4 width=4) (actual time=0.039..0.039 rows=1 loops=1)
-> Bitmap Heap Scan on pg_type t (cost=4.32..19.70 rows=4 width=4) (actual time=0.035..0.036 rows=1 loops=1)
Recheck Cond: (typname = 'geography'::name)
-> Bitmap Index Scan on pg_type_typname_nsp_index (cost=0.00..4.31 rows=4 width=0) (actual time=0.025..0.025 rows=1 loops=1)
Index Cond: (typname = 'geography'::name)
-> Hash (cost=5054.14..5054.14 rows=17676 width=132) (never executed)
-> Hash Join (cost=8.28..5054.14 rows=17676 width=132) (never executed)
Hash Cond: (c.relnamespace = n.oid)
-> Seq Scan on pg_class c (cost=0.00..4802.82 rows=17676 width=72) (never executed)
Filter: (relkind = ANY ('{r,v}'::"char"[]))
-> Hash (cost=5.90..5.90 rows=190 width=68) (never executed)
-> Seq Scan on pg_namespace n (cost=0.00..5.90 rows=190 width=68) (never executed)
-> Seq Scan on geometry_columns (cost=0.00..211.94 rows=8794 width=43) (actual time=0.002..3.634 rows=6656 loops=17836)
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute (cost=2763.38..2929.40 rows=13 width=68) (actual time=0.052..0.054 rows=1 loops=9343)
Index Cond: (pg_attribute.attrelid = pg_class.oid)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (hashed SubPlan 3))
SubPlan 1
-> Index Scan using pg_type_oid_index on pg_type (cost=0.00..8.28 rows=1 width=0) (never executed)
Index Cond: (oid = $0)
Filter: (typname = ANY ('{geometry,geography,topogeometry}'::name[]))
SubPlan 2
-> Bitmap Heap Scan on pg_type (cost=12.88..57.58 rows=12 width=4) (actual time=0.029..0.030 rows=2 loops=1)
Recheck Cond: (typname = ANY ('{geometry,geography,topogeometry}'::name[]))
-> Bitmap Index Scan on pg_type_typname_nsp_index (cost=0.00..12.88 rows=12 width=0) (actual time=0.021..0.021 rows=2 loops=1)
Index Cond: (typname = ANY ('{geometry,geography,topogeometry}'::name[]))
SubPlan 3
-> Hash Semi Join (cost=57.73..2629.03 rows=53739 width=4) (actual time=84.822..84.822 rows=0 loops=1)
Hash Cond: (a.typbasetype = b.oid)
-> Seq Scan on pg_type a (cost=0.00..1832.39 rows=53739 width=8) (actual time=0.021..54.158 rows=53878 loops=1)
-> Hash (cost=57.58..57.58 rows=12 width=4) (actual time=0.021..0.021 rows=2 loops=1)
-> Bitmap Heap Scan on pg_type b (cost=12.88..57.58 rows=12 width=4) (actual time=0.016..0.018 rows=2 loops=1)
Recheck Cond: (typname = ANY ('{geometry,geography,topogeometry}'::name[]))
-> Bitmap Index Scan on pg_type_typname_nsp_index (cost=0.00..12.88 rows=12 width=0) (actual time=0.012..0.012 rows=2 loops=1)
Index Cond: (typname = ANY ('{geometry,geography,topogeometry}'::name[]))
Total runtime: 171231.823 ms
(55 rows)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
