Thread: Performance of pgAdmin 14 Beta 2

Performance of pgAdmin 14 Beta 2

From

Kieran McCusker

Date:

01 February 2012, 09:29:14

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

Re: Performance of pgAdmin 14 Beta 2

From

Guillaume Lelarge

Date:

01 February 2012, 09:45:03

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

Re: Performance of pgAdmin 14 Beta 2

From

Kieran McCusker

Date:

01 February 2012, 09:51:50

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.
> 
>

Re: Performance of pgAdmin 14 Beta 2

From

Guillaume Lelarge

Date:

06 February 2012, 21:33:21

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