Re: Slow opening of Table -> Properties... -> Columns

Hi,

On Mon, May 11, 2009 at 8:40 AM, Kieran McCusker
<kieran(dot)mccusker(at)kwest(dot)info> wrote:

> 2009-05-11 08:10:17 QUERY  : Set query (kwest:5432): SELECT
> format_type(t.oid,NULL) AS typname, CASE WHEN typelem > 0 THEN typelem ELSE
> t.oid END as elemoid, typlen, typtype, t.oid, nspname,
>        (SELECT COUNT(1) FROM pg_type t2 WHERE t2.typname = t.typname) > 1 AS
> isdup
>   FROM pg_type t
>   JOIN pg_namespace nsp ON typnamespace=nsp.oid
>  WHERE (NOT (typname = 'unknown' AND nspname = 'pg_catalog')) AND
> typisdefined AND typtype IN ('b', 'c', 'd', 'e')AND typname NOT IN (SELECT
> relname FROM pg_class WHERE relnamespace = typnamespace AND relkind != 'c'
> UNION SELECT '_' || relname FROM pg_class WHERE relnamespace = typnamespace
> AND relkind != 'c')  AND nsp.nspname NOT LIKE 'information_schema'
>  ORDER BY CASE WHEN typtype='d' THEN 0 ELSE 1 END, (t.typelem>0)::bool, 1
>
> The last query is where the time went - Running it in a query window it took
> 235 seconds.

I created a database with 250 schemas, containing 25 tables each, with
41 columns per table, and the query took ~280 seconds.

The optimised version below (thanks to Greg Stark for spending some
time on this) runs in ~300ms. Suffice it to say, I committed the
change!

SELECT format_type(t.oid,NULL) AS typname,
CASE WHEN typelem > 0 THEN typelem ELSE t.oid END as elemoid,
typlen, typtype, t.oid, nspname,
(SELECT COUNT(1) FROM pg_type t2 WHERE t2.typname = t.typname)
> 1 AS isdup

FROM pg_type t
JOIN pg_namespace nsp ON typnamespace=nsp.oid

WHERE (NOT (typname = 'unknown' AND nspname = 'pg_catalog'))
AND typisdefined
AND typtype IN ('b', 'c', 'd', 'e')
AND NOT EXISTS (select 1 from pg_class where
relnamespace=typnamespace and relname = typname and relkind != 'c')
AND (typname not like '_%'
OR NOT EXISTS (select 1 from pg_class where
relnamespace=typnamespace and relname = substring(typname from
2)::name and relkind != 'c'))
AND nsp.nspname != 'information_schema'
ORDER BY typtype != 'd', t.typelem>0, 1

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com