Performance - Mailing list pgadmin-support

<font face="Helvetica, Arial, sans-serif">Hi<br /><br /> Could I raise a small performance issue with 1.10.<br /><br />
WhenI click on a table in the object browser in my big database (</font><font face="Helvetica, Arial, sans-serif">5004
viewsand tables, 260 schemas, 211,493 columns) there is a noticable lag before the SQL pane refreshes. Enabling
debuggingshow the following for the first query<br /><br /></font><tt>2009-06-03 14:52:41 INFO   : Displaying
propertiesfor Table addresses_walks<br /> 2009-06-03 14:52:41 STATUS : Retrieving Table details...<br /> 2009-06-03
14:52:41INFO   : Adding child object to table addresses_walks<br /> 2009-06-03 14:52:41 QUERY  : Set query
(kwest:5432):SELECT att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval, CASE WHEN att.attndims >
0THEN 1 ELSE 0 END AS isarray, format_type(ty.oid,NULL) AS typname, tn.nspname as typnspname, et.typname as
elemtypname,<br/>   cl.relname, na.nspname, att.attstattarget, description, cs.relname AS sername, ns.nspname AS
serschema,<br/>   (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup, indkey,
inha.attrelid::regclassAS inhrelname,<br />   EXISTS(SELECT 1 FROM  pg_constraint WHERE conrelid=att.attrelid AND
contype='f'AND att.attnum=ANY(conkey)) As isfk<br />   FROM pg_attribute att<br />   JOIN pg_type ty ON
ty.oid=atttypid<br/>   JOIN pg_namespace tn ON tn.oid=ty.typnamespace<br />   JOIN pg_class cl ON
cl.oid=att.attrelid<br/>   JOIN pg_namespace na ON na.oid=cl.relnamespace<br />   LEFT OUTER JOIN pg_type et ON
et.oid=ty.typelem<br/>   LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum<br />   LEFT OUTER
JOINpg_description des ON des.objoid=att.attrelid AND des.objsubid=att.attnum<br />   LEFT OUTER JOIN (pg_depend JOIN
pg_classcs ON objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum<br />   LEFT OUTER
JOINpg_namespace ns ON ns.oid=cs.relnamespace<br />   LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND
indisprimary<br/>   LEFT JOIN pg_attribute inha ON att.attname=inha.attname AND inha.attrelid IN (SELECT inhparent FROM
pg_inheritsWHERE inhrelid=att.attrelid)<br />  WHERE att.attrelid = 28288875::oid<br />    AND att.attnum > 0<br />
  AND att.attisdropped IS FALSE<br />  ORDER BY att.attnum<br /></tt><br /><font face="Helvetica, Arial,
sans-serif">Runningthis query in pgAdmin typically takes</font> 670ms (i.e. the lag). If I change the QUERY to the
following:-<br /><br /><tt>SELECT att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval, CASE WHEN
att.attndims> 0 THEN 1 ELSE 0 END AS isarray, format_type(ty.oid,NULL) AS typname, tn.nspname as typnspname,
et.typnameas elemtypname,<br />   cl.relname, na.nspname, att.attstattarget, description, cs.relname AS sername,
ns.nspnameAS serschema,<br />   (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup, indkey,
<br/>   case<br />     when exists(SELECT inhparent FROM pg_inherits WHERE inhrelid=att.attrelid) then
att.attrelid::regclass<br/>     else null<br />   end AS inhrelname,<br />   EXISTS(SELECT 1 FROM  pg_constraint WHERE
conrelid=att.attrelidAND contype='f' AND att.attnum=ANY(conkey)) As isfk<br /><br />   FROM pg_attribute att<br />  
JOINpg_type ty ON ty.oid=atttypid<br />   JOIN pg_namespace tn ON tn.oid=ty.typnamespace<br />   JOIN pg_class cl ON
cl.oid=att.attrelid<br/>   JOIN pg_namespace na ON na.oid=cl.relnamespace<br />   LEFT OUTER JOIN pg_type et ON
et.oid=ty.typelem<br/>   LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum<br />   LEFT OUTER
JOINpg_description des ON des.objoid=att.attrelid AND des.objsubid=att.attnum<br />   LEFT OUTER JOIN (pg_depend JOIN
pg_classcs ON objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum<br />   LEFT OUTER
JOINpg_namespace ns ON ns.oid=cs.relnamespace<br />   LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND
indisprimary<br/>  WHERE att.attrelid = 28288875::oid<br />    AND att.attnum > 0<br />    AND att.attisdropped IS
FALSE<br/>  ORDER BY att.attnum<br /><br /><br /></tt><font face="Helvetica, Arial, sans-serif">Then this typically
takes170ms. (I have dropped the final self join and moved the only use of it into a case statement)</font><br /><br
/><fontface="Helvetica, Arial, sans-serif">I believe this is functionally equivalent, although we don't use inherited
tablesso I can't comment on what effect lots of inherited tables would have on the query.<br /> The database is 8.3.7
runningon 64bit Fedora<br /><br /> Any thoughts?<br /><br /> Anyway keep up the good work, this release is already a
bigstep up from 1.8!<br /><br /> Kieran<br /><br /></font>