Re: Large number of rows in pg_type and slow gui (pgadmin) refresh - Mailing list pgadmin-support

Below, I deleted the logfile, connected to a database server, and immediately copied the log here without doing
anythingelse.  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
certainschemas 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.620110731 (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
confloadedsinceFROM 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
JOINpg_tablespace ta ON db.dattablespace=ta.OID LEFT OUTER JOIN pg_shdescription descr ON db.oid=descr.objoidORDER 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_tablespacetsORDER 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=relnamespaceWHERErelname='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')
ASdescription  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')
ASdescription  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.620110731 (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'
ANDproargtypes[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
OUTERJOIN pg_description des ON des.objoid=nsp.oidWHERE ((nspname = 'pg_catalog' AND EXISTS (SELECT 1 FROM pg_class
WHERErelname = '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
LIMIT1)) 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.typnamespaceJOIN pg_type tt ON tt.oid=casttarget JOIN pg_namespace nt ON nt.oid=tt.typnamespace LEFT JOIN
pg_procpr ON pr.oid=castfunc LEFT JOIN pg_namespace np ON np.oid=pr.pronamespace LEFT OUTER JOIN pg_description des ON
des.objoid=ca.oidAND des.objsubid=0ORDER 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_wrapperfdw LEFT OUTER JOIN pg_proc vp on vp.oid=fdwvalidator LEFT OUTER JOIN pg_description des ON
des.objoid=fdw.oidAND 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_prochp on hp.oid=lanplcallfoid LEFT OUTER JOIN pg_proc vp on vp.oid=lanvalidator LEFT OUTER JOIN pg_description des
ONdes.objoid=lan.oid AND des.objsubid=0WHERE lanispl IS TRUEORDER 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
cancreateFROM pg_namespace nsp LEFT OUTER JOIN pg_description des ON des.objoid=nsp.oidWHERE 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
LIMIT1)) 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.oidJOIN 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((SELECTdatconfig FROM pg_database WHERE oid=553541),1), array_upper((SELECT datconfig FROM
pg_databaseWHERE 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
FROMpg_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) || '[]'
ELSEtf.typname END as finalname, agginitval, description, oprname, opn.nspname as oprnsp FROM pg_aggregate ag LEFT
OUTERJOIN pg_operator op ON op.oid=aggsortop LEFT OUTER JOIN pg_namespace opn ON opn.oid=op.oprnamespace  JOIN pg_proc
prON pr.oid = ag.aggfnoid JOIN pg_type tt on tt.oid=aggtranstype JOIN pg_type tf on tf.oid=prorettype LEFT OUTER JOIN
pg_descriptiondes ON des.objoid=aggfnoid::oidWHERE pronamespace = 553544::oidORDER 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,
descriptionFROM pg_conversion co JOIN pg_proc pr ON pr.oid=conproc JOIN pg_namespace na ON na.oid=pr.pronamespace LEFT
OUTERJOIN pg_description des ON des.objoid=co.oid AND des.objsubid=0WHERE connamespace = 553544::oidORDER 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_typet2 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.typnamespaceLEFT OUTER JOIN pg_description des ON des.objoid=d.oidWHERE d.typtype = 'd' AND d.typnamespace =
553544::oidORDERBY 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
parserON parser.oid=cfg.cfgparser LEFT OUTER JOIN pg_description des ON des.objoid=cfg.oidWHERE cfg.cfgnamespace =
553544::oidORDERBY 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
OUTERJOIN pg_ts_template t ON t.oid=dict.dicttemplate LEFT OUTER JOIN pg_description des ON des.objoid=dict.oidWHERE
dict.dictnamespace= 553544::oidORDER 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.oidWHEREprs.prsnamespace = 553544::oidORDER 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.oidWHERE
tmpl.tmplnamespace= 553544::oidORDER 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=pronamespaceWHERE 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'::regclassAND 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
prJOIN pg_type typ ON typ.oid=prorettype JOIN pg_namespace typns ON typns.oid=typ.typnamespace JOIN pg_language lng ON
lng.oid=prolangLEFT OUTER JOIN pg_description des ON des.objoid=pr.oidWHERE 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.oprjoinas joinproc, op.oprrest as restrproc, description FROM pg_operator op LEFT OUTER JOIN pg_type lt ON
lt.oid=op.oprleftLEFT OUTER JOIN pg_type rt ON rt.oid=op.oprright JOIN pg_type et on et.oid=op.oprresult LEFT OUTER
JOINpg_operator co ON co.oid=op.oprcom LEFT OUTER JOIN pg_operator ne ON ne.oid=op.oprnegate LEFT OUTER JOIN
pg_descriptiondes ON des.objoid=op.oidWHERE op.oprnamespace = 553544::oidORDER 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.typnameas intypename, dt.typname as keytypename, amname, opfname FROM pg_opclass op JOIN pg_opfamily opf ON
op.opcfamily=opf.oidJOIN pg_am am ON am.oid=opf.opfmethod JOIN pg_type it ON it.oid=opcintype LEFT OUTER JOIN pg_type
dtON dt.oid=opckeytypeWHERE opcnamespace = 553544::oidORDER 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.opfmethodWHERE opfnamespace = 553544::oidORDER 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.oidWHERE relkind =
'S'AND relnamespace  = 553544::oidORDER 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=tgfoidAND 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_tablespacespc 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
tstON tst.oid = rel.reltoastrelidWHERE rel.relkind IN ('r','s','t') AND rel.relnamespace = 553544::oidORDER 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
prJOIN pg_type typ ON typ.oid=prorettype JOIN pg_namespace typns ON typns.oid=typ.typnamespace JOIN pg_language lng ON
lng.oid=prolangLEFT OUTER JOIN pg_description des ON des.objoid=pr.oidWHERE 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
ONct.oid=t.typrelid AND ct.relkind <> 'c' LEFT OUTER JOIN pg_description des ON des.objoid=t.oidWHERE t.typtype != 'd'
ANDt.typname NOT LIKE E'\\_%' AND t.typnamespace = 553544::oidORDER 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)
ASviewowner, c.relacl, description, pg_get_viewdef(c.oid, true) AS definition FROM pg_class c LEFT OUTER JOIN
pg_descriptiondes ON (des.objoid=c.oid and des.objsubid=0)WHERE ((c.relhasrules AND (EXISTS (          SELECT
r.rulenameFROM pg_rewrite r           WHERE ((r.ev_class = c.oid)             AND (bpchar(r.ev_type) = '1'::bpchar))
)))OR (c.relkind = 'v'::char))  AND relnamespace = 553544::oidORDER 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=pronamespaceWHERE 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'::regclassAND 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@nexgenwireless.com]
Sent: Friday, January 04, 2013 9:19 AM
To: 'Guillaume Lelarge'
Cc: 'pgadmin-support@postgresql.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
thetabs 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
optionsthe 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@lelarge.info]
Sent: Friday, January 04, 2013 4:30 AM
To: Robert Klaus
Cc: pgadmin-support@postgresql.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
forgotto use that cache for some dialogs or tabs. 


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