Hello, We run PostgreSQL 9.1 as our DBMS, with PostGIS. We have some largish databases here, with several thousand tables in the primary schema. I've found that connecting to these databases with PgAdmin 1.16.0 takes sometimes upwards of 60 seconds. Specifically, while connecting to the database itself is quick, enumerating the schema is very slow. The psql client command doesn't have any such problems when I run \dt; it returns quickly, even when run remotely.
So, I set the logging to "debug" and viewed the queries PgAdmin was sending to the server when I clicked the schema object. Most of it looked reasonable, grabbing data on the tables from pg_class, but then I found a place where the program seemed to be iterating over OIDs from the results! It was issuing query after query after query of this form: SELECT substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS autovacuum_enabled , substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold , substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_vacuum_scale_factor , substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold , substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_analyze_scale_factor , substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay , substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit , substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age , substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age , substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age , rel.reloptions AS reloptions FROM pg_catalog.pg_class rel WHERE rel.oid=(SELECT org_tbl.reltoastrelid FROM pg_catalog.pg_class org_tbl WHERE org_tbl.oid=10533066::oid) The timestamps on the logs confirmed that, indeed, this was where most of the time was going. Each query was completedly quickly enough, but the number issued was so high that they ended up taking nearly a minute to return all of the results for each object in the schema. Given the size of the pg_class result set, this strikes me as really, really inefficient. Perhaps these options could be queried in one go, by joining against the main query for the listing of tables and other objects? Alternatively, could I turn off the querying of this information? We don't use custom autovacuum settings for specific objects at our site, preferring to just have the whole databases get autovacuumed regularly. -- Avi Blackmore Head Programmer/System Administrator Agri ImaGIS Technologies, Inc. -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
