Hi On Wed, Oct 31, 2012 at 9:10 PM, Avi Blackmore <supp...@satshot.com> wrote: > 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.
Urgh. That seems like a thinko. > 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? I've attached a patch that attempts to do exactly that. Are you able to test it? Ashesh; could you please give the patch a review as well? I want to be sure I haven't subtly broken something, as hacking this query about in the wrong way could cause all sorts of fun! Thanks. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
avoid_querying_toast_individually.diff
Description: Binary data
-- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
