From: Jamie Tufnell <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
Date: Tue, 8 Jan 2008 18:49:06 +0000
Message-ID: <b0a4f3350801081049wb3d02b4s936f1b9b03b49335@mail.gmail.com> (raw)
In-Reply-To: <87236C1C-B08E-4521-A141-C7C65EDD3545@myemma.com>
References: <b0a4f3350801080624k2088c96aq21c511873959aa87@mail.gmail.com>
<fm028v$106a$1@news.hub.org>
<b0a4f3350801080805p3d3a0325rb70936c9fe3a812a@mail.gmail.com>
<b0a4f3350801080831l54d4abedme99e2032b70c5608@mail.gmail.com>
<fm09ql$29q5$1@news.hub.org>
<b0a4f3350801080941x5b4cccc9qbf6220ab35a0bf57@mail.gmail.com>
<87236C1C-B08E-4521-A141-C7C65EDD3545@myemma.com>
On 1/8/08, Erik Jones <erik@myemma.com> wrote:
> > Hmm so rather than NOT IN ( .. LIMIT 50) would you suggest IN ( ...
> > OFFSET 50) like in Erik's example? Or something else entirely?
>
> Well, that would give you some gain. Think about it like this: once
> a given user's history records are at 50 and you insert a row, if you
> use the NOT IN clause your comparing each of 51 rows to each of the
> 50 you want to keep to find the one that can go while with the IN
> version your comparing each of the 51 rows to the 1 that can go. Now
> how much of a gain that will be I can't say, YMMV. I don't remember
> you saying anything about it so I'll also go ahead and point out that
> you most likely will want an index on user_id if you don't already.
Thanks for the explanation Erik. I did already have the index, but
I've reimplemented using IN/OFFSET instead of NOT IN/LIMIT and it does
indeed seem to be faster.
> > Do you think a regular batch process to delete rows might be more
> > appropriate than a trigger in this scenario?
>
> That depends on your usage pattern. Assuming you aren't running user
> history report queries constantly that's probably what I'd do. Also,
> if you're sure you won't need anything but the last 50 records per
> user, I'd definitely agree with cleaning out data that's not needed.
OK cool, thanks for your advice Erik.
Cheers,
Jamie
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-sql@postgresql.org
Cc: diesql@googlemail.com
Subject: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
In-Reply-To: <b0a4f3350801081049wb3d02b4s936f1b9b03b49335@mail.gmail.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