Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 144FB475C22 for ; Mon, 27 Jan 2003 14:55:11 -0500 (EST) Received: from lakemtao02.cox.net (lakemtao02.cox.net [68.1.17.243]) by postgresql.org (Postfix) with ESMTP id 7E32A475843 for ; Mon, 27 Jan 2003 14:55:10 -0500 (EST) Received: from [192.168.1.1] ([68.11.66.83]) by lakemtao02.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030127195511.NBQX6744.lakemtao02.cox.net@[192.168.1.1]> for ; Mon, 27 Jan 2003 14:55:11 -0500 Subject: Re: bigserial vs serial - which one I'd have to use? From: Ron Johnson To: PgSQL Performance ML In-Reply-To: <200301271133.37738.josh@agliodbs.com> References: <1043616281.829.38.camel@maci> <200301271133.37738.josh@agliodbs.com> Content-Type: text/plain Organization: Message-Id: <1043697304.9899.60.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 27 Jan 2003 13:55:04 -0600 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/354 X-Sequence-Number: 1001 On Mon, 2003-01-27 at 13:33, Josh Berkus wrote: > Medve, > > > Have you got any data (ie in percentage) of around how much more CPU > > work needed with the bigserial type in the queries? > > > > I have a log database with 100million records (the biggest table > > contains 65million records) and I use bigserial data type as primary key > > now. The primary key looks this way: YYYYMMDD1xxxxxxx where the first 8 > > numbers are the date, and the x's are the record sequence number on that > > day. This way the records are in ascendant order. Almost all of the > > queries contains date constraints (PK like 'YYYYMMDD%'). I'd like to > > know if I do it in a stupid way or not. I'm not a DBA expert so every > > idea are welcome. If you need more information about the > > hardware/software environment, the DB structure then I'll post them. > > Given that structure, I'd personally create a table with a 2-column primary > key, one column of type DATE and one SERIAL column. Alternately, if you find > the conversion of DATE to char for output purposes really slows things down, > one column of INT and one of SERIAL. Either way, the two columns together > make up the primary key. > > I would definitely suggest avoiting the temptation to do this as a single > column of type CHAR(). That would be vastly more costly than either > strategy mentioned above: > > DATE + SERIAL (INT) = 8 bytes Ah, cool. I thought DATE was 8 bytes. Should have RTFM, of course. > INT + SERIAL (INT) = 8 bytes > > CHAR(16) = 18 bytes -- +---------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "Fear the Penguin!!" | +---------------------------------------------------------------+