Thread: Slow opening of Table -> Properties... -> Columns
<font face="Helvetica, Arial, sans-serif">Hi<br /><br /> Enabling logging revealed the following :-<br /><br
/></font><tt>2009-05-1108:10:05 INFO : Setting logfile to: C:\Users\mccuskk\Documents\pgadmin.log<br /> 2009-05-11
08:10:05INFO : Setting loglevel to: &Debug (not recommended for normal use)<br /> 2009-05-11 08:10:07 INFO :
Displayingproperties for Table events<br /> 2009-05-11 08:10:07 STATUS : Retrieving Table details...<br /> 2009-05-11
08:10:07STATUS : Retrieving Table details... (0.00 secs)<br /> 2009-05-11 08:10:09 QUERY : Set query (kwest:5432):
SELECTgroname FROM pg_group ORDER BY groname<br /> 2009-05-11 08:10:09 QUERY : Set query (kwest:5432): SELECT groname
FROMpg_group ORDER BY groname<br /> 2009-05-11 08:10:09 QUERY : Set query (kwest:5432): SELECT rolname FROM pg_roles
WHERErolcanlogin ORDER BY 1<br /> 2009-05-11 08:10:09 QUERY : Set query (kwest:5432): SELECT oid, spcname FROM
pg_tablespaceWHERE spcname <> 'pg_global' ORDER BY spcname<br /> 2009-05-11 08:10:09 QUERY : Set query
(kwest:5432):SELECT c.oid, c.relname , nspname<br /> FROM pg_class c<br /> JOIN pg_namespace n ON
n.oid=c.relnamespace<br/> WHERE relkind='r'<br /> AND (n.nspname NOT LIKE E'pg\_%' AND n.nspname NOT in
('information_schema'))AND c.oid NOT IN (24996767::oid) ORDER BY relnamespace, c.relname<br /> 2009-05-11 08:10:10
QUERY : Set query (kwest:5432): SELECT name, setting FROM pg_settings WHERE name like '%vacuum%' ORDER BY name<br />
2009-05-1108:10:10 QUERY : Set query (kwest:5432): SELECT * FROM pg_autovacuum WHERE vacrelid=24996767::oid<br />
2009-05-1108:10:17 QUERY : Set query (kwest:5432): SELECT format_type(t.oid,NULL) AS typname, CASE WHEN typelem > 0
THENtypelem ELSE t.oid END as elemoid, typlen, typtype, t.oid, nspname,<br /> (SELECT COUNT(1) FROM pg_type t2
WHEREt2.typname = t.typname) > 1 AS isdup<br /> FROM pg_type t<br /> JOIN pg_namespace nsp ON
typnamespace=nsp.oid<br/> 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'<br /> ORDER BY CASE WHEN typtype='d' THEN 0 ELSE 1 END, (t.typelem>0)::bool, 1<br
/></tt><fontface="Helvetica, Arial, sans-serif"><br /> The last query is where the time went - Running it in a query
windowit took 235 seconds.<br /><br /> Let me know if I can provide anything else<br /><br /> Cheers<br /><br />
Kieran<br/><br /></font>
Hi
On Mon, May 11, 2009 at 8:40 AM, Kieran McCusker
<kieran.mccusker@kwest.info> wrote:
> Hi
>
> 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.
>
> Let me know if I can provide anything else
EXPLAIN ANALYZE output for the query would be most helpful.
Thanks.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
Hi,
On Mon, May 11, 2009 at 8:40 AM, Kieran McCusker
<kieran.mccusker@kwest.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
(select1 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