X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 25421D1B4FE for ; Thu, 2 Oct 2003 20:12:11 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 75590-02 for ; Thu, 2 Oct 2003 17:11:26 -0300 (ADT) Received: from cbnsitar.cbnco.com (unknown [207.164.182.16]) by svr1.postgresql.org (Postfix) with ESMTP id C56E1D1B522 for ; Thu, 2 Oct 2003 17:11:20 -0300 (ADT) Received: (from root@localhost) by cbnsitar.cbnco.com (8.11.6/8.11.3/SuSE Linux 8.11.1-0.5) id h92K9v018280 for pgsql-performance@postgresql.org; Thu, 2 Oct 2003 16:09:57 -0400 Received: from mail.cbnco.com (hermes.cbnco.com [207.164.182.47]) by cbnsitar.cbnco.com (8.11.6/8.11.3/SuSE Linux 8.11.1-0.5) with SMTP id h92K9ui18273 for ; Thu, 2 Oct 2003 16:09:56 -0400 Received: from 207.164.182.8 (SquirrelMail authenticated user rwu.cbnco.com) by mail.cbnco.com with HTTP; Thu, 2 Oct 2003 16:11:21 -0400 (EDT) Message-ID: <33506.207.164.182.8.1065125481.squirrel@mail.cbnco.com> In-Reply-To: <3F7C75C3.4060904@potentialtech.com> References: <33423.207.164.182.8.1065119401.squirrel@mail.cbnco.com> <1065120643.89807.40.camel@jester> <3F7C75C3.4060904@potentialtech.com> Date: Thu, 2 Oct 2003 16:11:21 -0400 (EDT) Subject: Thanks - Re: low cardinality column From: "Rong Wu" To: "Postgresql Performance" Reply-To: rwu@cbnco.com User-Agent: SquirrelMail/1.4.1 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 Importance: Normal X-Scanned-By: MIMEDefang 2.24 (www . roaringpenguin . com / mimedefang) X-Virus-Scanned: by AMaViS 0.3.12pre6 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=IN_REP_TO, PRIORITY_NO_NAME, QUOTED_EMAIL_TEXT, RCVD_IN_OSIRUSOFT_COM, REFERENCES, REPLY_WITH_QUOTES, USER_AGENT X-Spam-Level: X-Archive-Number: 200310/65 X-Sequence-Number: 3813 Thanks, Rod, Josh and Bill, That' fantastic. have a nice day, rong :-) > Rod Taylor wrote: >> On Thu, 2003-10-02 at 14:30, Rong Wu wrote: >> >>>Hi, >>> >>>I have a select like this: >>> >>>SELECT MAX(transactionid) FROM cbntransaction WHERE transactiontypeid=0; >> >> >> For various reasons (primarily MVCC and the ability to make custom >> aggregates making it difficult) MAX() is not optimized in this fashion. >> >> Try: >> >> SELECT transactionid >> FROM ... >> WHERE ... >> ORDER BY transactionid DESC >> LIMIT 1; > > Despite this good suggestion, if you're using this technique to generate > the next transaction ID, you're going to have errors as concurrency rises. > > Use a SERIAL, which guarantees that you won't have two processes generate > the same number. > > -- > Bill Moran > Potential Technologies > http://www.potentialtech.com > >