Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 65D942E3B3B for ; Tue, 8 Jan 2008 14:29:06 -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 09552-03 for ; Tue, 8 Jan 2008 14:28:48 -0400 (AST) Received: from smtp146.iad.emailsrvr.com (smtp146.iad.emailsrvr.com [207.97.245.146]) by postgresql.org (Postfix) with ESMTP id 801942E39F1 for ; Tue, 8 Jan 2008 14:28:55 -0400 (AST) Received: from relay4.r5.iad.mlsrvr.com (localhost [127.0.0.1]) by relay4.r5.iad.mlsrvr.com (SMTP Server) with ESMTP id ECB99C8EE; Tue, 8 Jan 2008 13:28:54 -0500 (EST) Received: by relay4.r5.iad.mlsrvr.com (Authenticated sender: erik-AT-myemma.com) with ESMTP id C7317C348; Tue, 8 Jan 2008 13:28:54 -0500 (EST) In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed Message-Id: <87236C1C-B08E-4521-A141-C7C65EDD3545@myemma.com> Cc: pgsql-sql@postgresql.org Content-Transfer-Encoding: quoted-printable From: Erik Jones Subject: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..? Date: Tue, 8 Jan 2008 12:28:52 -0600 To: Jamie Tufnell X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/52 X-Sequence-Number: 29905 On Jan 8, 2008, at 11:41 AM, Jamie Tufnell wrote: > On 1/8/08, codeWarrior wrote: >> Jamie: >> >> I think you are probably having slowdown issues in your "DELETE =20 >> FROM WHERE >> NOT IN SELECT ORDER BY DESCENDING" construct -- that seems a bit =20 >> convoluted >> to me.... > > 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 =20= a given user's history records are at 50 and you insert a row, if you =20= use the NOT IN clause your comparing each of 51 rows to each of the =20 50 you want to keep to find the one that can go while with the IN =20 version your comparing each of the 51 rows to the 1 that can go. Now =20= how much of a gain that will be I can't say, YMMV. I don't remember =20 you saying anything about it so I'll also go ahead and point out that =20= you most likely will want an index on user_id if you don't already. >> ALSO: It looks to me like you have a column named "timestamp' ??? =20 >> This is >> bad practice since "timestamp" is a reserved word... You really =20 >> ought NOT to >> use reserved words for column names... different debate. > > I do realize it would be better to use something else and thanks for > the tip :-) This is an established database and "timestamp" has been > used in other tables which is why I stuck to it here.. one day when > time permits maybe I'll rename them all! > >> Why bother deleting records anyway ? Why not alter your query that =20= >> tracks >> the 50 records to LIMIT 50 ??? > > The read query does LIMIT 50 and the reason for deleting the rest of > the records is because they're not needed by the application and > there's loads of them being created all the time (currently several > million unnecessary rows) -- I imagine eventually this will slow > things down? > > 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 =20= history report queries constantly that's probably what I'd do. Also, =20= if you're sure you won't need anything but the last 50 records per =20 user, I'd definitely agree with cleaning out data that's not needed. Erik Jones DBA | Emma=AE erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com