Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 73629475C22 for ; Mon, 27 Jan 2003 14:32:48 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id C8D07475843 for ; Mon, 27 Jan 2003 14:32:47 -0500 (EST) Received: from [66.219.92.2] (HELO chocolate-mousse) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2327378; Mon, 27 Jan 2003 11:32:53 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Medve Gabor , pgsql-performance@postgresql.org Subject: Re: bigserial vs serial - which one I'd have to use? Date: Mon, 27 Jan 2003 11:33:37 -0800 X-Mailer: KMail [version 1.4] References: <1043616281.829.38.camel@maci> In-Reply-To: <1043616281.829.38.camel@maci> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200301271133.37738.josh@agliodbs.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/352 X-Sequence-Number: 999 Medve, > Have you got any data (ie in percentage) of around how much more CPU > work needed with the bigserial type in the queries? >=20 > 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= =20 key, one column of type DATE and one SERIAL column. Alternately, if you fi= nd=20 the conversion of DATE to char for output purposes really slows things down= ,=20 one column of INT and one of SERIAL. Either way, the two columns togethe= r=20 make up the primary key. I would definitely suggest avoiting the temptation to do this as a single= =20 column of type CHAR(). That would be vastly more costly than either=20 strategy mentioned above: DATE + SERIAL (INT) =3D 8 bytes INT + SERIAL (INT) =3D 8 bytes CHAR(16) =3D 18 bytes --=20 -Josh Berkus Aglio Database Solutions San Francisco