Re: Large number of rows in pg_type and slow gui (pgadmin) refresh

Below, I deleted the logfile, connected to a database server, and immediately copied the log here without doing anything else. It took 5+ minutes.

Would someone be able to point me to a place in the source code where I can add predicates to the sql to elimate certain schemas with many tables and views? I believe that is the cause of the long elapsed time due to the network latency. Correct me if you think its something else.

I'm not a developer but we have someone that knows some c++ that may be able to change and compile the source.

2013-01-05 12:22:56 INFO : Attempting to create a connection object...
2013-01-05 12:22:56 INFO : Using password file C:\Users\Klaus\AppData\Roaming\postgresql\pgpass.conf
2013-01-05 12:22:56 STATUS : Connecting to database...
2013-01-05 12:22:56 INFO : Opening connection with connection string: host='10.4.0.121' dbname='postgres' user='samsung' port=5432 sslcompression=0 application_name='pgAdmin III - Browser'
2013-01-05 12:22:56 QUERY : Scalar query (10.4.0.121:5432): SELECT version();
2013-01-05 12:22:56 QUERY : Query result: PostgreSQL 8.4.9 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit
2013-01-05 12:22:56 QUERY : Set query (10.4.0.121:5432): SET DateStyle=ISO;
SET client_min_messages=notice;
SELECT oid, pg_encoding_to_char(encoding) AS encoding, datlastsysoid
FROM pg_database WHERE datname='postgres'
2013-01-05 12:22:56 INFO : Setting client_encoding to 'UNICODE'
2013-01-05 12:22:56 QUERY : Set query (10.4.0.121:5432): SELECT usecreatedb, usesuper, CASE WHEN usesuper THEN pg_postmaster_start_time() ELSE NULL END as upsince, CASE WHEN usesuper THEN pg_conf_load_time() ELSE NULL END as confloadedsince
FROM pg_user WHERE usename=current_user
2013-01-05 12:22:56 QUERY : Set query (10.4.0.121:5432): SELECT rolcreaterole, rolcreatedb FROM pg_roles WHERE rolname = current_user;
2013-01-05 12:22:56 STATUS : Connecting to database... (0.22 secs)
2013-01-05 12:22:56 STATUS : Restoring previous environment...
2013-01-05 12:22:56 INFO : pgServer object initialised as required.
2013-01-05 12:22:56 INFO : Adding child object to server 10.4.0.121:5432
2013-01-05 12:22:56 QUERY : Set query (10.4.0.121:5432): SELECT db.oid, datname, db.dattablespace AS spcoid, spcname, datallowconn, datacl, pg_encoding_to_char(encoding) AS serverencoding, pg_get_userbyid(datdba) AS datowner,has_database_privilege(db.oid, 'CREATE') as cancreate,
current_setting('default_tablespace') AS default_tablespace,
descr.description
, db.datconnlimit as connectionlimit, db.datcollate as collate, db.datctype as ctype FROM pg_database db
LEFT OUTER JOIN pg_tablespace ta ON db.dattablespace=ta.OID
LEFT OUTER JOIN pg_shdescription descr ON db.oid=descr.objoid
ORDER BY datname
2013-01-05 12:22:56 QUERY : Set query (10.4.0.121:5432): SELECT ts.oid, spcname, spclocation, pg_get_userbyid(spcowner) as spcuser, spcacl, pg_catalog.shobj_description(oid, 'pg_tablespace') AS description FROM pg_tablespace ts
ORDER BY spcname
2013-01-05 12:22:56 QUERY : Scalar query (10.4.0.121:5432): SELECT cl.oid FROM pg_class cl JOIN pg_namespace ns ON ns.oid=relnamespace
WHERE relname='pga_job' AND nspname='pgagent'
2013-01-05 12:22:56 INFO : Query returned no tuples
2013-01-05 12:22:56 QUERY : Scalar query (10.4.0.121:5432): SELECT has_table_privilege('pg_authid', 'SELECT')
2013-01-05 12:22:56 QUERY : Query result: t
2013-01-05 12:22:56 QUERY : Set query (10.4.0.121:5432): SELECT oid, *, pg_catalog.shobj_description(oid, 'pg_authid') AS description FROM pg_authid WHERE NOT rolcanlogin ORDER BY rolname
2013-01-05 12:22:56 QUERY : Scalar query (10.4.0.121:5432): SELECT has_table_privilege('pg_authid', 'SELECT')
2013-01-05 12:22:56 QUERY : Query result: t
2013-01-05 12:22:56 QUERY : Set query (10.4.0.121:5432): SELECT oid, *, pg_catalog.shobj_description(oid, 'pg_authid') AS description FROM pg_authid WHERE rolcanlogin ORDER BY rolname
2013-01-05 12:22:56 QUERY : Set query (10.4.0.121:5432): SELECT setting FROM pg_settings WHERE name IN ('autovacuum', 'track_counts')
2013-01-05 12:22:56 INFO : Displaying properties for Databases Databases
2013-01-05 12:22:56 INFO : Displaying properties for Database sam_usa_gis
2013-01-05 12:22:56 INFO : Opening connection with connection string: host='10.4.0.121' dbname='sam_usa_gis' user='samsung' port=5432 sslcompression=0 application_name='pgAdmin III - Browser'
2013-01-05 12:22:56 QUERY : Scalar query (10.4.0.121:5432): SELECT version();
2013-01-05 12:22:56 QUERY : Query result: PostgreSQL 8.4.9 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit
2013-01-05 12:22:56 QUERY : Set query (10.4.0.121:5432): SET DateStyle=ISO;
SET client_min_messages=notice;
SELECT oid, pg_encoding_to_char(encoding) AS encoding, datlastsysoid
FROM pg_database WHERE oid = 553541
2013-01-05 12:22:57 INFO : Setting client_encoding to 'UNICODE'
2013-01-05 12:22:57 QUERY : Scalar query (10.4.0.121:5432): SELECT proname FROM pg_proc WHERE proname='pg_get_viewdef' AND proargtypes[1]=16
2013-01-05 12:22:57 QUERY : Query result: pg_get_viewdef
2013-01-05 12:22:57 QUERY : Scalar query (10.4.0.121:5432): SHOW search_path
2013-01-05 12:22:57 QUERY : Query result: "$user",public,geo,dw
2013-01-05 12:22:57 QUERY : Set query (10.4.0.121:5432): SELECT nspname, session_user=nspname AS isuser FROM pg_namespace
2013-01-05 12:22:57 INFO : Adding child object to database sam_usa_gis
2013-01-05 12:22:57 QUERY : Set query (10.4.0.121:5432): SELECT 2 AS nsptyp,
nsp.nspname, nsp.oid, pg_get_userbyid(nspowner) AS namespaceowner, nspacl, description, FALSE as cancreate
FROM pg_namespace nsp
LEFT OUTER JOIN pg_description des ON des.objoid=nsp.oid
WHERE ((nspname = 'pg_catalog' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1)) OR
(nspname = 'pgagent' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1)) OR
(nspname = 'information_schema' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1)) OR
(nspname LIKE '_%' AND EXISTS (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
) ORDER BY 1, nspname
2013-01-05 12:22:57 QUERY : Set query (10.4.0.121:5432): SELECT ca.oid, ca.*, format_type(st.oid,NULL) AS srctyp, format_type(tt.oid,tt.typtypmod) AS trgtyp, ns.nspname AS srcnspname, nt.nspname AS trgnspname,
proname, np.nspname AS pronspname, description
FROM pg_cast ca
JOIN pg_type st ON st.oid=castsource
JOIN pg_namespace ns ON ns.oid=st.typnamespace
JOIN pg_type tt ON tt.oid=casttarget
JOIN pg_namespace nt ON nt.oid=tt.typnamespace
LEFT JOIN pg_proc pr ON pr.oid=castfunc
LEFT JOIN pg_namespace np ON np.oid=pr.pronamespace
LEFT OUTER JOIN pg_description des ON des.objoid=ca.oid AND des.objsubid=0
ORDER BY st.typname, tt.typname
2013-01-05 12:22:57 QUERY : Set query (10.4.0.121:5432): SELECT fdw.oid, fdwname, fdwvalidator, fdwacl, vp.proname as fdwval, description, array_to_string(fdwoptions, ',') AS fdwoptions, pg_get_userbyid(fdwowner) as fdwowner
FROM pg_foreign_data_wrapper fdw
LEFT OUTER JOIN pg_proc vp on vp.oid=fdwvalidator
LEFT OUTER JOIN pg_description des ON des.objoid=fdw.oid AND des.objsubid=0

ORDER BY fdwname
2013-01-05 12:22:57 QUERY : Set query (10.4.0.121:5432): SELECT lan.oid, lanname, lanpltrusted, lanacl, hp.proname as lanproc, vp.proname as lanval, description, pg_get_userbyid(lan.lanowner) as languageowner
FROM pg_language lan
JOIN pg_proc hp on hp.oid=lanplcallfoid
LEFT OUTER JOIN pg_proc vp on vp.oid=lanvalidator
LEFT OUTER JOIN pg_description des ON des.objoid=lan.oid AND des.objsubid=0
WHERE lanispl IS TRUE
ORDER BY lanname
2013-01-05 12:22:57 QUERY : Set query (10.4.0.121:5432): SELECT CASE WHEN nspname LIKE E'pg\\_temp\\_%' THEN 1
WHEN (nspname LIKE E'pg\\_%') THEN 0
ELSE 3 END AS nsptyp,
nsp.nspname, nsp.oid, pg_get_userbyid(nspowner) AS namespaceowner, nspacl, description, has_schema_privilege(nsp.oid, 'CREATE') as cancreate
FROM pg_namespace nsp
LEFT OUTER JOIN pg_description des ON des.objoid=nsp.oid
WHERE NOT ((nspname = 'pg_catalog' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1)) OR
(nspname = 'pgagent' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1)) OR
(nspname = 'information_schema' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1)) OR
(nspname LIKE '_%' AND EXISTS (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
) ORDER BY 1, nspname
2013-01-05 12:22:57 QUERY : Set query (10.4.0.121:5432): SELECT nsp.oid, substr(nspname, 2) as clustername, nspname, pg_get_userbyid(nspowner) AS namespaceowner, description
FROM pg_namespace nsp
LEFT OUTER JOIN pg_description des ON des.objoid=nsp.oid
JOIN pg_proc pro ON pronamespace=nsp.oid AND proname = 'slonyversion'
ORDER BY nspname
2013-01-05 12:22:57 QUERY : Scalar query (10.4.0.121:5432): SELECT COUNT(*) FROM
(SELECT tgargs from pg_trigger tr
LEFT JOIN pg_depend dep ON dep.objid=tr.oid AND deptype = 'i'
LEFT JOIN pg_constraint co ON refobjid = co.oid AND contype = 'f'
WHERE
tgisconstraint
AND co.oid IS NULL
GROUP BY tgargs
HAVING count(1) = 3) AS foo
2013-01-05 12:22:57 QUERY : Query result: 0
2013-01-05 12:22:57 QUERY : Set query (10.4.0.121:5432): SELECT '' AS rolname, split_part(config, '=', 1) AS variable,
replace(config,split_part(config, '=', 1) || '=', '') AS value
FROM (
SELECT
(
SELECT datconfig[i]
FROM pg_database
WHERE oid=553541
) AS config
FROM generate_series(array_lower((SELECT datconfig FROM pg_database WHERE oid=553541),1), array_upper((SELECT datconfig FROM pg_database WHERE oid=553541),1)) AS i
) configs
2013-01-05 12:22:57 INFO : Displaying properties for Schemas Schemas
2013-01-05 12:22:57 INFO : Displaying properties for Schema user_maps
2013-01-05 12:22:57 INFO : Adding child object to schema user_maps
2013-01-05 12:22:57 QUERY : Set query (10.4.0.121:5432): SELECT oid, format_type(oid, typtypmod) AS typname FROM pg_type
2013-01-05 12:23:14 QUERY : Set query (10.4.0.121:5432): SELECT aggfnoid::oid, proname AS aggname, pg_get_userbyid(proowner) AS aggowner, aggtransfn,
aggfinalfn, proargtypes, aggtranstype, proacl, CASE WHEN (tt.typlen = -1 AND tt.typelem != 0) THEN (SELECT at.typname FROM pg_type at WHERE at.oid = tt.typelem) || '[]' ELSE tt.typname END as transname, prorettype AS aggfinaltype, CASE WHEN (tf.typlen = -1 AND tf.typelem != 0) THEN (SELECT at.typname FROM pg_type at WHERE at.oid = tf.typelem) || '[]' ELSE tf.typname END as finalname, agginitval, description, oprname, opn.nspname as oprnsp
FROM pg_aggregate ag
LEFT OUTER JOIN pg_operator op ON op.oid=aggsortop
LEFT OUTER JOIN pg_namespace opn ON opn.oid=op.oprnamespace JOIN pg_proc pr ON pr.oid = ag.aggfnoid
JOIN pg_type tt on tt.oid=aggtranstype
JOIN pg_type tf on tf.oid=prorettype
LEFT OUTER JOIN pg_description des ON des.objoid=aggfnoid::oid
WHERE pronamespace = 553544::oid
ORDER BY aggname
2013-01-05 12:23:14 QUERY : Set query (10.4.0.121:5432): SELECT co.oid, co.*, pg_encoding_to_char(conforencoding) as forencoding, pg_get_userbyid(conowner) as owner,pg_encoding_to_char(contoencoding) as toencoding, proname, nspname, description
FROM pg_conversion co
JOIN pg_proc pr ON pr.oid=conproc
JOIN pg_namespace na ON na.oid=pr.pronamespace
LEFT OUTER JOIN pg_description des ON des.objoid=co.oid AND des.objsubid=0
WHERE connamespace = 553544::oid
ORDER BY conname
2013-01-05 12:23:14 QUERY : Set query (10.4.0.121:5432): SELECT d.oid, d.typname as domname, d.typbasetype, format_type(b.oid,NULL) as basetype, pg_get_userbyid(d.typowner) as domainowner,
d.typlen, d.typtypmod, d.typnotnull, d.typdefault, d.typndims, d.typdelim, bn.nspname as basensp,
description, (SELECT COUNT(1) FROM pg_type t2 WHERE t2.typname=d.typname) > 1 AS domisdup,
(SELECT COUNT(1) FROM pg_type t3 WHERE t3.typname=b.typname) > 1 AS baseisdup
FROM pg_type d
JOIN pg_type b ON b.oid = d.typbasetype
JOIN pg_namespace bn ON bn.oid=b.typnamespace
LEFT OUTER JOIN pg_description des ON des.objoid=d.oid
WHERE d.typtype = 'd' AND d.typnamespace = 553544::oid
ORDER BY d.typname
2013-01-05 12:23:14 QUERY : Set query (10.4.0.121:5432): SELECT cfg.oid, cfg.cfgname, pg_get_userbyid(cfg.cfgowner) as cfgowner, cfg.cfgparser, parser.prsname as parsername, description
FROM pg_ts_config cfg
LEFT OUTER JOIN pg_ts_parser parser ON parser.oid=cfg.cfgparser
LEFT OUTER JOIN pg_description des ON des.objoid=cfg.oid
WHERE cfg.cfgnamespace = 553544::oid
ORDER BY cfg.cfgname
2013-01-05 12:23:14 QUERY : Set query (10.4.0.121:5432): SELECT dict.oid, dict.dictname, pg_get_userbyid(dict.dictowner) as dictowner, t.tmplname, dict.dictinitoption, description
FROM pg_ts_dict dict
LEFT OUTER JOIN pg_ts_template t ON t.oid=dict.dicttemplate
LEFT OUTER JOIN pg_description des ON des.objoid=dict.oid
WHERE dict.dictnamespace = 553544::oid
ORDER BY dict.dictname
2013-01-05 12:23:14 QUERY : Set query (10.4.0.121:5432): SELECT prs.oid, prs.prsname, prs.prsstart, prs.prstoken, prs.prsend, prs.prslextype, prs.prsheadline, description
FROM pg_ts_parser prs
LEFT OUTER JOIN pg_description des ON des.objoid=prs.oid
WHERE prs.prsnamespace = 553544::oid
ORDER BY prs.prsname
2013-01-05 12:23:14 QUERY : Set query (10.4.0.121:5432): SELECT tmpl.oid, tmpl.tmplname, tmpl.tmplinit, tmpl.tmpllexize, description
FROM pg_ts_template tmpl
LEFT OUTER JOIN pg_description des ON des.objoid=tmpl.oid
WHERE tmpl.tmplnamespace = 553544::oid
ORDER BY tmpl.tmplname
2013-01-05 12:23:14 QUERY : Set query (10.4.0.121:5432): SELECT proname, pronargs, proargtypes[0] AS arg0, proargtypes[1] AS arg1, proargtypes[2] AS arg2
FROM pg_proc
JOIN pg_namespace n ON n.oid=pronamespace
WHERE proname IN ('pg_tablespace_size', 'pg_file_read', 'pg_logfile_rotate', 'pg_postmaster_starttime', 'pg_terminate_backend', 'pg_reload_conf', 'pgstattuple', 'pgstatindex')
AND nspname IN ('pg_catalog', 'public')
2013-01-05 12:23:14 QUERY : Scalar query (10.4.0.121:5432): SELECT count(*) FROM pg_attribute WHERE attrelid = 'pg_catalog.pg_proc'::regclass AND attname = 'proargdefaults'
2013-01-05 12:23:14 QUERY : Query result: 1
2013-01-05 12:23:14 QUERY : Set query (10.4.0.121:5432): SELECT pr.oid, pr.xmin, pr.*, format_type(TYP.oid, NULL) AS typname, typns.nspname AS typnsp, lanname, proargnames, pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS proargdefaultvals, pronargdefaults, proconfig, pg_get_userbyid(proowner) as funcowner, description
FROM pg_proc pr
JOIN pg_type typ ON typ.oid=prorettype
JOIN pg_namespace typns ON typns.oid=typ.typnamespace
JOIN pg_language lng ON lng.oid=prolang
LEFT OUTER JOIN pg_description des ON des.objoid=pr.oid
WHERE proisagg = FALSE AND pronamespace = 553544::oid
AND typname <> 'trigger'
ORDER BY proname
2013-01-05 12:23:14 QUERY : Set query (10.4.0.121:5432): SELECT oid, format_type(oid, NULL) AS typname FROM pg_type
2013-01-05 12:23:33 QUERY : Set query (10.4.0.121:5432): SELECT op.oid, op.oprname, pg_get_userbyid(op.oprowner) as opowner, op.oprkind, op.oprcanhash, op.oprcanmerge,
op.oprleft, op.oprright, lt.typname as lefttype, rt.typname as righttype, et.typname as resulttype,
co.oprname as compop, ne.oprname as negop,
op.oprcode as operproc, op.oprjoin as joinproc, op.oprrest as restrproc, description
FROM pg_operator op
LEFT OUTER JOIN pg_type lt ON lt.oid=op.oprleft
LEFT OUTER JOIN pg_type rt ON rt.oid=op.oprright
JOIN pg_type et on et.oid=op.oprresult
LEFT OUTER JOIN pg_operator co ON co.oid=op.oprcom
LEFT OUTER JOIN pg_operator ne ON ne.oid=op.oprnegate
LEFT OUTER JOIN pg_description des ON des.objoid=op.oid
WHERE op.oprnamespace = 553544::oid
ORDER BY op.oprname
2013-01-05 12:23:33 QUERY : Set query (10.4.0.121:5432): SELECT op.oid, op.*, pg_get_userbyid(op.opcowner) as opowner, it.typname as intypename, dt.typname as keytypename, amname, opfname
FROM pg_opclass op
JOIN pg_opfamily opf ON op.opcfamily=opf.oid
JOIN pg_am am ON am.oid=opf.opfmethod
JOIN pg_type it ON it.oid=opcintype
LEFT OUTER JOIN pg_type dt ON dt.oid=opckeytype
WHERE opcnamespace = 553544::oid
ORDER BY opcname
2013-01-05 12:23:33 QUERY : Set query (10.4.0.121:5432): SELECT opf.oid, opf.*, pg_get_userbyid(opf.opfowner) as opowner, amname
FROM pg_opfamily opf
JOIN pg_am am ON am.oid=opf.opfmethod
WHERE opfnamespace = 553544::oid
ORDER BY opfname
2013-01-05 12:23:33 QUERY : Set query (10.4.0.121:5432): SELECT cl.oid, relname, pg_get_userbyid(relowner) AS seqowner, relacl, description
FROM pg_class cl
LEFT OUTER JOIN pg_description des ON des.objoid=cl.oid
WHERE relkind = 'S' AND relnamespace = 553544::oid
ORDER BY relname
2013-01-05 12:23:33 QUERY : Set query (10.4.0.121:5432): SELECT rel.oid, rel.relname, rel.reltablespace AS spcoid, spc.spcname, pg_get_userbyid(rel.relowner) AS relowner, rel.relacl, rel.relhasoids, rel.relhassubclass, rel.reltuples, des.description, con.conname, con.conkey,
EXISTS(select 1 FROM pg_trigger
JOIN pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
WHERE tgrelid=rel.oid) AS isrepl,
(select count(*) FROM pg_trigger
WHERE tgrelid=rel.oid AND tgisconstraint = FALSE) AS triggercount
, substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor
, 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
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS toast_autovacuum_enabled
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_vacuum_scale_factor
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_analyze_scale_factor
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age
, rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions
, (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable
FROM pg_class rel
LEFT OUTER JOIN pg_tablespace spc on spc.oid=rel.reltablespace
LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND des.objsubid=0)
LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
LEFT OUTER JOIN pg_class tst ON tst.oid = rel.reltoastrelid
WHERE rel.relkind IN ('r','s','t') AND rel.relnamespace = 553544::oid
ORDER BY rel.relname
2013-01-05 12:23:40 QUERY : Set query (10.4.0.121:5432): SELECT pr.oid, pr.xmin, pr.*, format_type(TYP.oid, NULL) AS typname, typns.nspname AS typnsp, lanname, proargnames, pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS proargdefaultvals, pronargdefaults, proconfig, pg_get_userbyid(proowner) as funcowner, description
FROM pg_proc pr
JOIN pg_type typ ON typ.oid=prorettype
JOIN pg_namespace typns ON typns.oid=typ.typnamespace
JOIN pg_language lng ON lng.oid=prolang
LEFT OUTER JOIN pg_description des ON des.objoid=pr.oid
WHERE proisagg = FALSE AND pronamespace = 553544::oid
AND typname = 'trigger'
AND lanname != 'edbspl'
ORDER BY proname
2013-01-05 12:23:40 QUERY : Set query (10.4.0.121:5432): SELECT oid, format_type(oid, NULL) AS typname FROM pg_type
2013-01-05 12:23:56 QUERY : Set query (10.4.0.121:5432): SELECT t.oid, t.*, format_type(t.oid, null) AS alias,
pg_get_userbyid(t.typowner) as typeowner, e.typname as element,
description, ct.oid AS taboid
FROM pg_type t
LEFT OUTER JOIN pg_type e ON e.oid=t.typelem
LEFT OUTER JOIN pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'
LEFT OUTER JOIN pg_description des ON des.objoid=t.oid
WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\\_%' AND t.typnamespace = 553544::oid
ORDER BY t.typname
2013-01-05 12:24:19 QUERY : Set query (10.4.0.121:5432): SELECT c.oid, c.xmin, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, pg_get_viewdef(c.oid, true) AS definition
FROM pg_class c
LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0)
WHERE ((c.relhasrules AND (EXISTS (
SELECT r.rulename FROM pg_rewrite r
WHERE ((r.ev_class = c.oid)
AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char))
AND relnamespace = 553544::oid
ORDER BY relname
2013-01-05 12:28:10 QUERY : Set query (10.4.0.121:5432): SELECT proname, pronargs, proargtypes[0] AS arg0, proargtypes[1] AS arg1, proargtypes[2] AS arg2
FROM pg_proc
JOIN pg_namespace n ON n.oid=pronamespace
WHERE proname IN ('pg_tablespace_size', 'pg_file_read', 'pg_logfile_rotate', 'pg_postmaster_starttime', 'pg_terminate_backend', 'pg_reload_conf', 'pgstattuple', 'pgstatindex')
AND nspname IN ('pg_catalog', 'public')
2013-01-05 12:28:10 QUERY : Scalar query (10.4.0.121:5432): SELECT count(*) FROM pg_attribute WHERE attrelid = 'pg_catalog.pg_proc'::regclass AND attname = 'proargdefaults'
2013-01-05 12:28:10 QUERY : Query result: 1
2013-01-05 12:28:10 INFO : Displaying properties for Schema user_maps
2013-01-05 12:28:10 STATUS : Restoring previous environment... (313.89 secs)

-----Original Message-----
From: Robert Klaus [mailto:rklaus(at)nexgenwireless(dot)com]
Sent: Friday, January 04, 2013 9:19 AM
To: 'Guillaume Lelarge'
Cc: 'pgadmin-support(at)postgresql(dot)org'
Subject: RE: [pgadmin-support] Large number of rows in pg_type and slow gui (pgadmin) refresh

Connecting to the database server just took 62 seconds, which defaults to the database that has almost all of the 36,000+ objects. Another 40 seconds to open any of the schemas within that database. Once the schema is opened any of the tabs within are very fast, unless we refresh them. Another tool our other developers use has the same issues.

Most of the objects are GIS maps that are generated by our users. The tables and views vary in structure based on options the user chooses when generating them.

We'll be adding many clients in the next few months so the number of tables and views will grow tenfold very quickly.

-----Original Message-----
From: Guillaume Lelarge [mailto:guillaume(at)lelarge(dot)info]
Sent: Friday, January 04, 2013 4:30 AM
To: Robert Klaus
Cc: pgadmin-support(at)postgresql(dot)org
Subject: Re: [pgadmin-support] Large number of rows in pg_type and slow gui (pgadmin) refresh

On Thu, 2013-01-03 at 13:43 -0600, Robert Klaus wrote:
>
> This message was posted in the pgsql-general and novice lists, and I
> was cirected here . . . sorry for those of you receiving multiple posts.
>
>
>
>
>
> We have 36,000+ rows returned by " SELECT oid, format_type(oid,
> typtypmod) AS typname FROM pg_type".
>
>
>
> My manager says this is only a small number compared to what is
> expected by next summer.
>
>
>
> When I run this select statement on the database server it returns in
> under
> 1 second but it takes a minute to open some tabs using pgAmin III (1.16.1).
>

Which tabs? because we now have a cache for types, so that we don't need to query them each time we need them. Maybe we forgot to use that cache for some dialogs or tabs.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com