Advice about how to delete - Mailing list pgsql-performance

Hi all,

   I have the following scenario, I have users and groups where a user
can belong to n groups, and a group can have n users. A user must belogn
at least to a group. So when I delete a group I must check that there
isn't any orphan. To do this I have something like that:

   CREATE TABLE users
   (
     user_id    SERIAL8 PRIMARY KEY
     user_name  VARCHAR(50)
   )

   CREATE TABLE groups
   (
     group_id    SERIAL8 PRIMARY KEY,
     group_name  VARCHAR(50)
   )

   CREATE TABLE user_groups
   (
     user_id   INT8 REFERENCES users(user_id),
     group_id  INT8 REFERENCE groups(group_id),
     CONSTRAINT pk PRIMARY_KEY ( user_id, group_id)
   )

   CREATE INDEX idx_user_id ON user_groups( user_id );
   CREATE INDEX idx_group_id ON user_groups( group_id );

   FUNCTION delete_group( INT8 )
   DECLARE
     p_groupid ALIAS FOR $1;
     v_deleted INTEGER;
     v_count   INTEGER;
     result    RECORD;

   BEGIN
     v_deleted = 0;

     FOR result IN SELECT user_id FROM user_groups WHERE group_id =
p_groupid
     LOOP

       SELECT INTO v_count COUNT(user_id) FROM user_groups WHERE user_id
= result.user_id LIMIT 2;

       IF v_count = 1 THEN
         DELETE FROM users WHERE user_id = result.user_id;
         v_deleted = v_deleted + 1;
       END IF;

     END LOOP;

     DELETE FROM groups WHERE group_id = p_groupid;

     RETURN v_deleted;
   END;


   This works quite fast with small groups but when the group has an
important number of users, it takes too much time. The delete_group
action is fired from the user interface of the application.

   Do you have any idea about how I could improve the performance of this?

Thanks all
--
Arnau