Performance

Hi

Could I raise a small performance issue with 1.10.

When I click on a table in the object browser in my big database (5004
views and tables, 260 schemas, 211,493 columns) there is a noticable lag
before the SQL pane refreshes. Enabling debugging show the following for
the first query

2009-06-03 14:52:41 INFO : Displaying properties for Table addresses_walks
2009-06-03 14:52:41 STATUS : Retrieving Table details...
2009-06-03 14:52:41 INFO : Adding child object to table addresses_walks
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 > 0 THEN 1 ELSE 0 END AS isarray,
format_type(ty.oid,NULL) AS typname, tn.nspname as typnspname,
et.typname as elemtypname,
cl.relname, na.nspname, att.attstattarget, description, cs.relname AS
sername, ns.nspname AS serschema,
(SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS
isdup, indkey, inha.attrelid::regclass AS inhrelname,
EXISTS(SELECT 1 FROM pg_constraint WHERE conrelid=att.attrelid AND
contype='f' AND att.attnum=ANY(conkey)) As isfk
FROM pg_attribute att
JOIN pg_type ty ON ty.oid=atttypid
JOIN pg_namespace tn ON tn.oid=ty.typnamespace
JOIN pg_class cl ON cl.oid=att.attrelid
JOIN pg_namespace na ON na.oid=cl.relnamespace
LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND
adnum=att.attnum
LEFT OUTER JOIN pg_description des ON des.objoid=att.attrelid AND
des.objsubid=att.attnum
LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND
cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace
LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
LEFT JOIN pg_attribute inha ON att.attname=inha.attname AND
inha.attrelid IN (SELECT inhparent FROM pg_inherits WHERE
inhrelid=att.attrelid)
WHERE att.attrelid = 28288875::oid
AND att.attnum > 0
AND att.attisdropped IS FALSE
ORDER BY att.attnum

Running this query in pgAdmin typically takes 670ms (i.e. the lag). If I
change the QUERY to the following :-

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.typname as elemtypname,
cl.relname, na.nspname, att.attstattarget, description, cs.relname AS
sername, ns.nspname AS serschema,
(SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS
isdup, indkey,
case
when exists(SELECT inhparent FROM pg_inherits WHERE
inhrelid=att.attrelid) then att.attrelid::regclass
else null
end AS inhrelname,
EXISTS(SELECT 1 FROM pg_constraint WHERE conrelid=att.attrelid AND
contype='f' AND att.attnum=ANY(conkey)) As isfk

FROM pg_attribute att
JOIN pg_type ty ON ty.oid=atttypid
JOIN pg_namespace tn ON tn.oid=ty.typnamespace
JOIN pg_class cl ON cl.oid=att.attrelid
JOIN pg_namespace na ON na.oid=cl.relnamespace
LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND
adnum=att.attnum
LEFT OUTER JOIN pg_description des ON des.objoid=att.attrelid AND
des.objsubid=att.attnum
LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND
cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace
LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
WHERE att.attrelid = 28288875::oid
AND att.attnum > 0
AND att.attisdropped IS FALSE
ORDER BY att.attnum

Then this typically takes 170ms. (I have dropped the final self join and
moved the only use of it into a case statement)

I believe this is functionally equivalent, although we don't use
inherited tables so I can't comment on what effect lots of inherited
tables would have on the query.
The database is 8.3.7 running on 64bit Fedora

Any thoughts?

Anyway keep up the good work, this release is already a big step up from
1.8!

Kieran