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

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"?

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
        );