From: | Kieran McCusker <kieran(dot)mccusker(at)kwest(dot)info> |
---|---|
To: | pgadmin-support(at)postgresql(dot)org |
Subject: | Performance of pgAdmin 14 Beta 2 |
Date: | 2012-02-01 10:28:56 |
Message-ID: | 4F2913E8.90706@kwest.info |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
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_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.
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"();
Anyway apologies if this has been raised before and, as usual, many
thanks for a great product
Kieran
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Lelarge | 2012-02-01 10:44:47 | Re: Performance of pgAdmin 14 Beta 2 |
Previous Message | Guillaume Lelarge | 2012-02-01 08:57:37 | Re: Custom Auto Vacuum factors |