From: | Jeff Frost <jeff(at)frostconsultingllc(dot)com> |
---|---|
To: | Daniel CAUNE <d(dot)caune(at)free(dot)fr> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: keeping last 30 entries of a log table |
Date: | 2006-06-19 16:16:43 |
Message-ID: | Pine.LNX.4.64.0606190915540.27250@glacier.frostconsultingllc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Sat, 17 Jun 2006, Daniel CAUNE wrote:
>> insert into log (account_id, message) values (1, 'this is a test);
>> delete from log where account_id = 1 and id not in ( select id from log
>> where account_id = 1 order by timestamp desc limit 30);
>>
>> I'm wondering if there is a more performance oriented method of doing the
>> delete that I'm not thinking of.
>>
>
> Depending on whether id is a kind of auto-incremented column that never cycles, I would suggest something like:
>
> DELETE FROM log
> WHERE account_id = 1
> AND id < (
> SELECT MIN(id)
> FROM log
> WHERE account_id = 1
> ORDER BY timestamp DESC
> LIMIT 30);
>
> I think there will be a performance difference with your method when the number of records to be deleted is huge.
Thanks Daniel, I'll try and benchmark them both and see if < turns out to be
faster than NOT IN. I guess there's no way to get around the subselect
though.
--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel CAUNE | 2006-06-19 17:13:27 | Re: keeping last 30 entries of a log table |
Previous Message | Andrew Sullivan | 2006-06-19 15:28:50 | Re: concurrency problem |