Обсуждение: Possible performance bug in enumerating schema objects?
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.
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
Вложения
Hi, just got back to the office. Thanks for the response! On Nov 8, 2012, at 10:25 AM, Dave Page wrote: >> 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? > I installed a binary image for my MacOS X workstation. I'd have to grab the source and build that. What's the version thepatch applies against? Current release? -- Avi Blackmore Head Programmer/System Administrator Agri ImaGIS Technologies, Inc.
On Tue, Nov 13, 2012 at 6:57 PM, Avi Blackmore <support@satshot.com> wrote: > Hi, just got back to the office. Thanks for the response! > > On Nov 8, 2012, at 10:25 AM, Dave Page wrote: > >>> 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? >> > > I installed a binary image for my MacOS X workstation. I'd have to grab the source and build that. What's theversion the patch applies against? Current release? I developed it against the master branch, but I suspect it'll apply to the 1.16 branch too, as I don't think anything has changed in the affected file in master only. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Nov 14, 2012, at 1:47 AM, Dave Page wrote: > On Tue, Nov 13, 2012 at 6:57 PM, Avi Blackmore <support@satshot.com> wrote: >> Hi, just got back to the office. Thanks for the response! >> >> On Nov 8, 2012, at 10:25 AM, Dave Page wrote: >> >>>> 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? >>> >> >> I installed a binary image for my MacOS X workstation. I'd have to grab the source and build that. What's theversion the patch applies against? Current release? > > I developed it against the master branch, but I suspect it'll apply to > the 1.16 branch too, as I don't think anything has changed in the > affected file in master only. I grabbed the source for 1.16, and applied the patch. It applied without problems. Built a new appbundle, and schema enumerationis now much much faster. Only a couple of seconds. I'd say the patch works! Thanks very much for your help. -- Avi Blackmore Head Programmer/System Administrator Agri ImaGIS Technologies, Inc.
On Wed, Nov 14, 2012 at 7:54 PM, Avi Blackmore <support@satshot.com> wrote: > > On Nov 14, 2012, at 1:47 AM, Dave Page wrote: > >> On Tue, Nov 13, 2012 at 6:57 PM, Avi Blackmore <support@satshot.com> wrote: >>> Hi, just got back to the office. Thanks for the response! >>> >>> On Nov 8, 2012, at 10:25 AM, Dave Page wrote: >>> >>>>> 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? >>>> >>> >>> I installed a binary image for my MacOS X workstation. I'd have to grab the source and build that. What's theversion the patch applies against? Current release? >> >> I developed it against the master branch, but I suspect it'll apply to >> the 1.16 branch too, as I don't think anything has changed in the >> affected file in master only. > > > I grabbed the source for 1.16, and applied the patch. It applied without problems. Built a new appbundle, andschema enumeration is now much much faster. Only a couple of seconds. > > I'd say the patch works! Thanks very much for your help. Great, thanks for testing. I'll get it applied. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company