Re: Possible performance bug in enumerating schema objects?
От | Dave Page |
---|---|
Тема | Re: Possible performance bug in enumerating schema objects? |
Дата | |
Msg-id | CA+OCxowsZ2jxt58JLQ-3zTsTDdLPg_JPZQfuMgd_R9CQinBH6w@mail.gmail.com обсуждение исходный текст |
Ответ на | Possible performance bug in enumerating schema objects? (Avi Blackmore <support@satshot.com>) |
Ответы |
Re: Possible performance bug in enumerating schema objects?
(Avi Blackmore <support@satshot.com>)
|
Список | pgadmin-support |
Hi On Wed, Oct 31, 2012 at 9:10 PM, Avi Blackmore <support@satshot.com> wrote: > Hello, > > We run PostgreSQL 9.1 as our DBMS, with PostGIS. We have some largish databases here, with several thousand tables inthe 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 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. 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 couldbe 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
Вложения
В списке pgadmin-support по дате отправления: