Re: Performance of pgAdmin 14 Beta 2

Hi,

On Wed, 2012-02-01 at 10:28 +0000, Kieran McCusker wrote:
> [...]
> I've noticed the performance of version 14.2 is very slow when clicking
> on tables.
> 

If by 14.2, you mean 1.14 beta 2, you first need to upgrade to 1.14.1.
Don't use a beta release when the stable one is released.

> ~3 seconds for V14.2.
> 
> Looking at the logs I see the following:-
> 
> 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 pr.oid=1646::oid
>  ORDER BY proname
> 
> -- Followed by
> 
> SELECT oid, format_type(oid, NULL) AS typname FROM pg_type
> 
> These are repeated 8 times to match the number of constraint triggers
> shown. The problem with this is that "SELECT oid, format_type(oid, NULL)
> AS typname FROM pg_type" returns almost 32,000 rows in my database and
> takes ~170ms
> 
> This in turn is causing the total time to execute the SQL for gathering
> information for the Properties tab to ~2 seconds.
> 

There has been some bugfix to this kind of issue. I don't remember
exactly which issue, but there has been some fixes around this.

> One odd thing I have just noticed is that there are actually only four
> distinct pr_proc oids being called - Corresponding to the four trigger
> procedures (they are all called twice), and the SQL Pane for this table
> shows :-
> 
> -- Table: versions
> 
> -- DROP TABLE versions;
> 
> CREATE TABLE versions
> (
>   id serial NOT NULL,
>   questionnaire_id integer NOT NULL,
>   label text,
>   styles text,
>   settings hstore,
>   version_number integer DEFAULT 1,
>   rendered_at timestamp with time zone,
>   lock_id integer,
>   created_by integer,
>   created_at timestamp without time zone DEFAULT now(),
>   created_from_id integer,
>   lang text DEFAULT 'eng-GB'::text,
>   CONSTRAINT versions_pkey PRIMARY KEY (id ),
>   CONSTRAINT "Ref_versions_to_questionnaires" FOREIGN KEY (questionnaire_id)
>       REFERENCES questionnaires (id) MATCH SIMPLE
>       ON UPDATE NO ACTION ON DELETE CASCADE
> )
> WITH (
>   OIDS=FALSE
> );
> ALTER TABLE versions
>   OWNER TO designer;
> GRANT ALL ON TABLE versions TO designer;
> GRANT ALL ON TABLE versions TO kwest;
> 
> -- Trigger: RI_ConstraintTrigger_24390486 on versions
> 
> -- DROP TRIGGER "RI_ConstraintTrigger_24390486" ON versions;
> 
> CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_24390486"
>   AFTER DELETE
>   ON versions
>   FOR EACH ROW
>   EXECUTE PROCEDURE "RI_FKey_cascade_del"();
> 
> -- Trigger: RI_ConstraintTrigger_24390487 on versions
> 
> -- DROP TRIGGER "RI_ConstraintTrigger_24390487" ON versions;
> 
> CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_24390487"
>   AFTER UPDATE
>   ON versions
>   FOR EACH ROW
>   EXECUTE PROCEDURE "RI_FKey_noaction_upd"();
> 
> -- Trigger: RI_ConstraintTrigger_24390529 on versions
> 
> -- DROP TRIGGER "RI_ConstraintTrigger_24390529" ON versions;
> 
> CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_24390529"
>   AFTER INSERT
>   ON versions
>   FOR EACH ROW
>   EXECUTE PROCEDURE "RI_FKey_check_ins"();
> 
> -- Trigger: RI_ConstraintTrigger_24390530 on versions
> 
> -- DROP TRIGGER "RI_ConstraintTrigger_24390530" ON versions;
> 
> CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_24390530"
>   AFTER UPDATE
>   ON versions
>   FOR EACH ROW
>   EXECUTE PROCEDURE "RI_FKey_check_upd"();
> 
> -- Trigger: RI_ConstraintTrigger_34286642 on versions
> 
> -- DROP TRIGGER "RI_ConstraintTrigger_34286642" ON versions;
> 
> CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_34286642"
>   AFTER DELETE
>   ON versions
>   FOR EACH ROW
>   EXECUTE PROCEDURE "RI_FKey_cascade_del"();
> 
> -- Trigger: RI_ConstraintTrigger_34286643 on versions
> 
> -- DROP TRIGGER "RI_ConstraintTrigger_34286643" ON versions;
> 
> CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_34286643"
>   AFTER UPDATE
>   ON versions
>   FOR EACH ROW
>   EXECUTE PROCEDURE "RI_FKey_noaction_upd"();
> 
> -- Trigger: RI_ConstraintTrigger_34533276 on versions
> 
> -- DROP TRIGGER "RI_ConstraintTrigger_34533276" ON versions;
> 
> CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_34533276"
>   AFTER DELETE
>   ON versions
>   FOR EACH ROW
>   EXECUTE PROCEDURE "RI_FKey_cascade_del"();
> 
> -- Trigger: RI_ConstraintTrigger_34533277 on versions
> 
> -- DROP TRIGGER "RI_ConstraintTrigger_34533277" ON versions;
> 
> CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_34533277"
>   AFTER UPDATE
>   ON versions
>   FOR EACH ROW
>   EXECUTE PROCEDURE "RI_FKey_noaction_upd"();
> 

This kind of trigger shouldn't be displayed. It's fixed in the source
code, but not yet available to the general public.

Anyway, first upgrade your pgadmin release, then, if you still have
issues, report them here.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org