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

Hello


2013/2/6 Alexander Farber <alexander.farber@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general