Re: "explain analyze" a procedure verbosely - to find which statement in it takes longer

Hello

2013/2/6 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:
> Hello,
>
> I've read in the docs, that every table should
> better have primary key and so I've rearranged
> my 8.4.13 database: added primary keys to
> each table (some of the primary keys are
> pairs of columns) and dropped all other indices.
>
> And I've probably dropped few indices too many,
> because a stored procedure takes very long now:
>
> # explain analyze select pref_delete_user('DE17795', 'agr. comment');
> QUERY PLAN
>
> --------------------------------------------------------------------------------
> ----------------
> Result (cost=0.00..0.26 rows=1 width=0) (actual time=1144672.341..1144672.342
> rows=1 loops=1)
> Total runtime: 1144672.457 ms
> (2 rows)
>
> Is there maybe a way to make the
> "explain analyze" output more verbose?
>
> Or do I have to run each of procedure's
> statements by hand, preprending them
> with "explain analyze"?
>

no, it is not possible

http://blog.guillaume.lelarge.info/index.php/post/2012/03/31/Profiling-PL/pgsql-functions

or autoexplain with active auto_explain.log_nested_statements

Regards

Pavel Stehule

> My code is below, thanks for any advices
>
> Regards
> Alex
>
> create or replace function pref_delete_user(_id varchar,
> _reason varchar) returns void as $BODY$
> begin
>
> insert into pref_ban2 select
> id,
> first_name,
> last_name,
> city,
> last_ip
> from pref_users where id=_id;
>
> update pref_ban2 set reason=_reason where id=_id;
>
> create temporary table temp_gids (gid int not null) on
> commit drop;
> insert into temp_gids (gid) select gid from
> pref_scores where id=_id;
>
> delete from pref_games p
> using temp_gids t
> where p.gid = t.gid;
>
> create temporary table temp_rids (rid int not null) on
> commit drop;
> insert into temp_rids (rid) select rid from pref_cards
> where id=_id;
>
> delete from pref_rounds r
> using temp_rids t
> where r.rid = t.rid;
>
> delete from pref_users where id=_id;
>
> end;
> $BODY$ language plpgsql;
>
> create table pref_users (
> id varchar(32) primary key,
> first_name varchar(64),
> last_name varchar(64),
> female boolean,
> avatar varchar(128),
> city varchar(64),
> login timestamp default current_timestamp,
> logout timestamp,
> last_ip inet,
> vip timestamp,
> mail varchar(256),
> medals integer not null default 0
> );
>
> create table pref_rounds (
> rid serial primary key,
> cards text,
> stamp timestamp default current_timestamp
> );
>
> create table pref_cards (
> rid integer references pref_rounds on delete cascade,
> id varchar(32) references pref_users on delete cascade,
> bid varchar(32) not null,
> trix integer not null,
> pos integer not null,
> money integer not null,
> last_ip inet,
> quit boolean,
> stamp timestamp default current_timestamp,
> primary key(id, rid) /* added recently */
> );
>
> create table pref_games (
> gid serial primary key,
> rounds integer not null,
> stamp timestamp default current_timestamp
> );
>
> create table pref_scores (
> id varchar(32) references pref_users on delete cascade,
> gid integer references pref_games on delete cascade,
> money integer not null,
> last_ip inet,
> quit boolean,
> primary key(id, gid); /* added recently */
> );
>
> create table pref_ban2 (
> id varchar(32) primary key, /* not a foreign key,
> since banned */
> first_name varchar(64),
> last_name varchar(64),
> city varchar(64),
> last_ip inet,
> reason varchar(128),
> created timestamp default current_timestamp
> );
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general