From: "codeWarrior" <gpatnude@hotmail.com>
Subject: Re: How to keep at-most N rows per group? periodic DELETEs or
	constraints or..?
Date: Tue, 8 Jan 2008 09:53:57 -0800
Organization: iDynaTECH, Inc.
Lines: 87
Message-ID: <fm09ql$29q5$1@news.hub.org>
References: <b0a4f3350801080624k2088c96aq21c511873959aa87@mail.gmail.com>
	<fm028v$106a$1@news.hub.org>
	<b0a4f3350801080805p3d3a0325rb70936c9fe3a812a@mail.gmail.com>
	<b0a4f3350801080831l54d4abedme99e2032b70c5608@mail.gmail.com>
Reply-To: "codeWarrior" <gpatnude@hotmail.com>
To: pgsql-sql@postgresql.org

Jamie:

I think you are probably having slowdown issues in your "DELETE FROM WHERE 
NOT IN SELECT ORDER BY DESCENDING" construct -- that seems a bit convoluted 
to me....


NOT IN is what is probably slowing you down the most....


ALSO: It looks to me like you have a column named "timestamp' ??? This is 
bad practice since "timestamp" is a reserved word... You really ought NOT to 
use reserved words for column names... different debate.


Why bother deleting records anyway ? Why not alter your query that tracks 
the 50 records to LIMIT 50 ???


""Jamie Tufnell"" <diesql@googlemail.com> wrote in message 
news:b0a4f3350801080831l54d4abedme99e2032b70c5608@mail.gmail.com...
> Hi codeWarrior,
>
> codeWarrior wrote:
>> > For user_ids that have more than 50 rows, I want to keep the most
>> > recent 50 and delete the rest.
>> How about using a trigger to call a stored procedure ? [ON INSERT to
>> user_item_history DO ...]
>
> [snip]
>
> Thanks for your input!  I've implemented this but I'm concerned about
> performance.  As I mentioned it's frequently being added to and this
> function will be called maybe a couple of times a second.  In my brief
> initial tests it seems like this is running quite slowly...
>
> Just to make sure I haven't done anything obviously wrong, I've
> included my implementation below.. if you could look over it I'd
> really appreciate it.
>
> CREATE OR REPLACE FUNCTION user_item_history_limit() RETURNS TRIGGER
>    AS $_$
> DECLARE
>    threshold integer = 50;
>    numrows integer;
> BEGIN
>    SELECT INTO numrows count(*) FROM user_item_history WHERE user_id
> = new.user_id;
>    IF numrows > threshold THEN
>        DELETE FROM user_item_history WHERE user_id = new.user_id AND
> id NOT IN (
>            SELECT id FROM user_item_history
>            WHERE user_id = new.user_id
>            ORDER BY timestamp DESC LIMIT threshold);
>        RAISE NOTICE '% rows exceeds threshold of % for user_id %;
> trimming..', numrows, threshold, new.user_id;
>    END IF;
>    RETURN new;
> END;
> $_$
>    LANGUAGE plpgsql;
>
> CREATE TRIGGER user_item_history_limit AFTER INSERT ON user_item_history
>    FOR EACH ROW EXECUTE PROCEDURE user_item_history_limit();
>
> Any suggestions greatly appreciated!
>
> Thanks again,
> Jamie
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>