-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Kirill Müller Sent: Wednesday, January 11, 2012 6:28 PM To: pgsql-general@xxxxxxxxxxxxxx Cc: Scott Marlowe Subject: Re: Enumeration of tables is very slow in largish database 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 ---------------------------------------------------------------------------- --------------------- I only see one (1) "AND NOT EXISTS" in the provided query. Syntax may be a little off but: ... AND (f_table_schema, f_table_name) NOT IN ( SELECT (nspname, relname) FROM geometry_columns ) Should work since it is no longer a correlated sub-query; whether the size of geometry_columns makes this better or worse performing is impossible to tell without testing but it isn't that much different than using a WITH/CTE. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
