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

Thank you for the replies -

Using a shared library seemed too difficult
for me at this moment, so I've prepended
"explain analyze" to each query in my proc
and the worst offender is this one:

# explain analyze delete from pref_rounds r
using temp_rids t
where r.rid = t.rid;
QUERY PLAN

------------------------------------------------------------------------------------------------------------------
------------------------
Nested Loop (cost=0.00..20270.01 rows=2880 width=6) (actual
time=4.246..120.058 rows=3022 loops=1)
-> Seq Scan on temp_rids t (cost=0.00..40.80 rows=2880 width=4)
(actual time=0.008..1.194 rows=3022 loops=1)
-> Index Scan using pref_rounds_pkey on pref_rounds r
(cost=0.00..7.01 rows=1 width=10) (actual time=0.037..0
.038 rows=1 loops=3022)
Index Cond: (r.rid = t.rid)
Trigger for constraint pref_cards_rid_fkey: time=1106450.109 calls=3022
Trigger for constraint pref_discuss_rid_fkey: time=171.322 calls=3022
Trigger for constraint pref_votes_rid_fkey: time=85.484 calls=3022
Total runtime: 1107082.899 ms
(8 rows)

So it probably spends most of its time
in the pref_cards, thorugh the FK "rid"?

Which index would be to add here best?

Regards
Alex

On Wed, Feb 6, 2013 at 11:03 AM, Alexander Farber >
> 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
> );