From: Arnau <arnaulist@andromeiberica.com>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Advice about how to delete
Date: Fri, 06 Jul 2007 16:42:31 +0200
Message-ID: <468E54D7.8020807@andromeiberica.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: arnaulist@andromeiberica.com
Subject: Re: Advice about how to delete
In-Reply-To: <468E54D7.8020807@andromeiberica.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox