From: Arnau <arnaulist@andromeiberica.com>
To: Michael Glaesemann <grzm@seespotcode.net>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Advice about how to delete
Date: Fri, 06 Jul 2007 17:56:21 +0200
Message-ID: <468E6625.5040803@andromeiberica.com> (raw)
In-Reply-To: <9583C488-0CE8-46FE-A58F-9D2DC8B6E201@seespotcode.net>
References: <468E54D7.8020807@andromeiberica.com>
<9583C488-0CE8-46FE-A58F-9D2DC8B6E201@seespotcode.net>
Hi Michael,
Michael Glaesemann wrote:
>
> On Jul 6, 2007, at 9:42 , Arnau wrote:
>
>> 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:
>
>
>> IF v_count = 1 THEN
>> DELETE FROM users WHERE user_id = result.user_id;
>> v_deleted = v_deleted + 1;
>> END IF;
>
> Am I right in reading that you're deleting any users that would be
> orphans? If so, you can just delete the orphans after rather than delete
> them beforehand (untested):
>
> -- delete user_groupDELETE FROM user_groups
> WHERE user_group_id = p_group_id;
>
> -- delete users that don't belong to any group
> DELETE FROM users
> WHERE user_id IN (
> SELECT user_id
> LEFT JOIN user_groups
> WHERE group_id IS NULL);
>
> This should execute pretty quickly. You don't need to loop over any
> results. Remember, SQL is a set-based language, so if you can pose your
> question in a set-based way, you can probably find a pretty good,
> efficient solution.
I have tested your solution and it's much worse than mine.
My test database has about 254000 users and about 30 groups. The test
I have done is remove a group with 258 users, my solution has taken
about 3 seconds and your solution after 20seconds didn't finished. Of
course the test machine is an old celeron with few MB of RAM, but as
test machine does the job.
Thank you very much
--
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, grzm@seespotcode.net
Subject: Re: Advice about how to delete
In-Reply-To: <468E6625.5040803@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