Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 0854A2E2FE5 for ; Tue, 8 Jan 2008 14:49:16 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 15360-05 for ; Tue, 8 Jan 2008 14:49:00 -0400 (AST) Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.183]) by postgresql.org (Postfix) with ESMTP id F36442E2DE5 for ; Tue, 8 Jan 2008 14:49:07 -0400 (AST) Received: by py-out-1112.google.com with SMTP id d37so5896725pye.29 for ; Tue, 08 Jan 2008 10:49:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=Rmr2YsQyMVFHvwkjYdSZMiWjDR2IWQ9OXKbRo8s9QXc=; b=kyED330tHRQyBkkphLJBOUFPKi8Wltgxbq2v9bQ25ZZNxtCj2Za9PKjzrGiH6Cj24bbkKn5x2aAViEGUTrX+Bw3euoTvp8y52OFP34brkyA4wMFJ7iNJhvk6RczHSyhl54vhKWHjoZPKsbl2HRlMwqHWT9ElHZbWf44U8qbjy3s= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=lizqKoO4BId6zAK8dNixAgWEZ5yHGz8q97VIx1Dc/nP9KrfLwvbHg/641iay9iAAgCsp0tjG6L6RBwr1oMfm/TMLs5P+1OI+EtbOVqA9Kvn3vnHJVkYnxSooV1T3EjH3+CB1vcdXeWeZXkya50Jsd0cwxMUMEGLiIbm8pMjzIrU= Received: by 10.35.36.13 with SMTP id o13mr1129507pyj.17.1199818146897; Tue, 08 Jan 2008 10:49:06 -0800 (PST) Received: by 10.35.82.11 with HTTP; Tue, 8 Jan 2008 10:49:06 -0800 (PST) Message-ID: Date: Tue, 8 Jan 2008 18:49:06 +0000 From: "Jamie Tufnell" To: pgsql-sql@postgresql.org Subject: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..? In-Reply-To: <87236C1C-B08E-4521-A141-C7C65EDD3545@myemma.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <87236C1C-B08E-4521-A141-C7C65EDD3545@myemma.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/53 X-Sequence-Number: 29906 On 1/8/08, Erik Jones 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