Обсуждение: Performance of pgAdmin 14 Beta 2
Hi I've noticed the performance of version 14.2 is very slow when clicking on tables. ~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_namespacetypns ON typns.oid=typ.typnamespace JOIN pg_language lng ON lng.oid=prolang LEFT OUTER JOIN pg_description desON des.objoid=pr.oid WHERE pr.oid=1646::oidORDER 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. 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 integerDEFAULT 1, rendered_at timestamp with time zone, lock_id integer, created_by integer, created_at timestamp withouttime zone DEFAULT now(), created_from_id integer, lang text DEFAULT 'eng-GB'::text, CONSTRAINT versions_pkey PRIMARYKEY (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"(); Anyway apologies if this has been raised before and, as usual, many thanks for a great product Kieran
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
Sorry A slip of the mind - I'm actually using 1.14.1 - I had just checked the development log before sending to see if a fix or problem was reported in 14.2. I'm not sure what you mean by "This kind of trigger shouldn't be displayed. It's fixed in the source code, but not yet available to the general public."? Should pgAdmin have suppressed these? Thanks again Kieran Many thanks On 01/02/2012 10:44, Guillaume Lelarge wrote: > 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. > >
On Wed, 2012-02-01 at 10:51 +0000, Kieran McCusker wrote: > Sorry > > A slip of the mind - I'm actually using 1.14.1 - I had just checked the > development log before sending to see if a fix or problem was reported > in 14.2. > OK. > I'm not sure what you mean by "This kind of trigger shouldn't be > displayed. It's fixed in the source code, but not yet available to the > general public."? Should pgAdmin have suppressed these? > Suppressed? like deleted? no. They are system triggers, so pgAdmin shouldn't display them in the SQL pane. Can you try with the last dev release? (meaning you'll have to compile it). If you can't, wan you send me (privately) your schema, so that I can try to see what's going on? thanks. PS: please, don't top-post. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com