[SQL] Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
At 07:20 AM 1/9/2008, [EMAIL PROTECTED] wrote: Date: Tue, 8 Jan 2008 17:41:18 + From: "Jamie Tufnell" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Subject: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..? Message-ID: <[EMAIL PROTECTED]> On 1/8/08, codeWarrior <[EMAIL PROTECTED]> wrote: > 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 Hmm so rather than NOT IN ( .. LIMIT 50) would you suggest IN ( ... OFFSET 50) like in Erik's example? Or something else entirely? > 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. 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 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? Thanks, Jamie This is kludgy but you would have some kind of random number test at the start of the trigger - if it evals true once per every ten calls to the trigger (say), you'd cut your delete statements execs by about 10x and still periodically truncate every set of user rows fairly often. On average you'd have ~55 rows per user, never less than 50 and a few outliers with 60 or 70 rows before they get trimmed back down to 50.. Seems more reliable than a cron job, and solves your problem of an ever growing table? You could adjust the random number test easily if you change your mind of the balance of size of table vs. # of delete statements down the road. Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
On Jan 9, 2008 12:20 PM, Steve Midgley <[EMAIL PROTECTED]> wrote: > This is kludgy but you would have some kind of random number test at > the start of the trigger - if it evals true once per every ten calls to > the trigger (say), you'd cut your delete statements execs by about 10x > and still periodically truncate every set of user rows fairly often. On > average you'd have ~55 rows per user, never less than 50 and a few > outliers with 60 or 70 rows before they get trimmed back down to 50.. > Seems more reliable than a cron job, and solves your problem of an ever > growing table? You could adjust the random number test easily if you > change your mind of the balance of size of table vs. # of delete > statements down the road. And, if you always through a limit 50 on the end of queries that retrieve data, you could let it grow quite a bit more than 60 or 70... Say 200. Then you could have it so that the random chopper function only gets kicked off every 100th or so time. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
On Jan 9, 2008, at 1:09 PM, Scott Marlowe wrote: On Jan 9, 2008 12:20 PM, Steve Midgley <[EMAIL PROTECTED]> wrote: This is kludgy but you would have some kind of random number test at the start of the trigger - if it evals true once per every ten calls to the trigger (say), you'd cut your delete statements execs by about 10x and still periodically truncate every set of user rows fairly often. On average you'd have ~55 rows per user, never less than 50 and a few outliers with 60 or 70 rows before they get trimmed back down to 50.. Seems more reliable than a cron job, and solves your problem of an ever growing table? You could adjust the random number test easily if you change your mind of the balance of size of table vs. # of delete statements down the road. And, if you always through a limit 50 on the end of queries that retrieve data, you could let it grow quite a bit more than 60 or 70... Say 200. Then you could have it so that the random chopper function only gets kicked off every 100th or so time. I like that idea. Erik Jones DBA | Emma® [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
On 1/9/08, Erik Jones <[EMAIL PROTECTED]> wrote: > On Jan 9, 2008, at 1:09 PM, Scott Marlowe wrote: > > On Jan 9, 2008 12:20 PM, Steve Midgley <[EMAIL PROTECTED]> wrote: > >> This is kludgy but you would have some kind of random number test at > >> the start of the trigger - if it evals true once per every ten > >> calls to > >> the trigger (say), you'd cut your delete statements execs by about > >> 10x > >> and still periodically truncate every set of user rows fairly > >> often. > > > > And, if you always through a limit 50 on the end of queries that > > retrieve data, you could let it grow quite a bit more than 60 or 70... > > Say 200. Then you could have it so that the random chopper function > > only gets kicked off every 100th or so time. > > I like that idea. I do too! I'm going to have a shot at implementing this tomorrow. Thanks for all your opinions guys :-) Cheers, Jamie ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Support for SQL TOP clause?
Hello, everyone Does Postgresql support query like SELECT *TOP 3* * FROM Individual ? If I use ORDER BY, I have to write non-sql code to get the first row in the result set, which I try to avoid. Thanks Chinyi
Re: [SQL] Support for SQL TOP clause?
SELECT * FROM Individual LIMIT 3 I asked the reverse question moving from PG to MSSQL :-( -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chinyi Woo Sent: Thursday, 10 January 2008 14:14 To: pgsql-sql@postgresql.org Subject: [SQL] Support for SQL TOP clause? Hello, everyone Does Postgresql support query like SELECT TOP 3 * FROM Individual ? If I use ORDER BY, I have to write non-sql code to get the first row in the result set, which I try to avoid. Thanks Chinyi THINK BEFORE YOU PRINT - Save paper if you don't really need to print this ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail.
[SQL] Shorthand column labels (SELECT col1 foo, ...) vs (SELECT col1 AS foo, ...)
I notice PG doesn't allow shorthand column labels -- it requires the 'AS' operand. SELECT col1 foo, ...; -> ERROR: syntax error at or near "foo" For compatibility with other databases, what objections might be argued in allowing this syntax in the future? On the 'pros' side I think it eases migration to PG, shortens code, is similar syntax to shorthand table aliases, and some users might argue it has become defacto syntax among DBs. Regards, Ken ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Shorthand column labels (SELECT col1 foo, ...) vs (SELECT col1 AS foo, ...)
Ken Johanson wrote: I notice PG doesn't allow shorthand column labels -- it requires the 'AS' operand. SELECT col1 foo, ...; -> ERROR: syntax error at or near "foo" For compatibility with other databases, what objections might be argued in allowing this syntax in the future? On the 'pros' side I think it eases migration to PG, shortens code, is similar syntax to shorthand table aliases, and some users might argue it has become defacto syntax among DBs. Regards, Ken Briefly discussed a couple of weeks ago. See http://archives.postgresql.org/pgsql-general/2008-01/msg00089.php ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Shorthand column labels (SELECT col1 foo, ...) vs (SELECT col1 AS foo, ...)
Paul Lambert wrote: Ken Johanson wrote: I notice PG doesn't allow shorthand column labels -- it requires the 'AS' operand. SELECT col1 foo, ...; -> ERROR: syntax error at or near "foo" Briefly discussed a couple of weeks ago. See http://archives.postgresql.org/pgsql-general/2008-01/msg00089.php Interesting thread(s)! What I didn't see discussed was the possibility of making a server and/or session option, where we could elect to turn-off the old behavior (PG specific behavior) and enable the standard/shorthand syntax. Users need a migration path. I personally cant ever see using those PGisms/features and would choose to enable the standard mode. I think I'd have fewer compatibility problems. Ken ---(end of broadcast)--- TIP 6: explain analyze is your friend
