Re: Possible performance bug in enumerating schema objects?

Hi

On Wed, Oct 31, 2012 at 9:10 PM, Avi Blackmore <support(at)satshot(dot)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