Hello,
We run PostgreSQL 9.1 as our DBMS, with PostGIS. We have some largish databases here, with several thousand tables in
theprimary 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
clientcommand 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
programseemed 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
completedlyquickly enough, but the number issued was so high that they ended up taking nearly a minute to return all of
theresults 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
bequeried 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
objectsat our site, preferring to just have the whole databases get autovacuumed regularly.
--
Avi Blackmore
Head Programmer/System Administrator
Agri ImaGIS Technologies, Inc.