Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id AF2B3475F38 for ; Mon, 27 Jan 2003 04:18:44 -0500 (EST) Received: from lakemtao04.cox.net (lakemtao04.cox.net [68.1.17.241]) by postgresql.org (Postfix) with ESMTP id E538E475E75 for ; Mon, 27 Jan 2003 04:18:43 -0500 (EST) Received: from [192.168.1.1] ([68.11.66.83]) by lakemtao04.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030127091845.UCKR22825.lakemtao04.cox.net@[192.168.1.1]> for ; Mon, 27 Jan 2003 04:18:45 -0500 Subject: Re: bigserial vs serial - which one I'd have to use? From: Ron Johnson To: PgSQL Performance ML In-Reply-To: <1043616281.829.38.camel@maci> References: <1043616281.829.38.camel@maci> Content-Type: text/plain Organization: Message-Id: <1043659121.815.407.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 27 Jan 2003 03:18:41 -0600 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/343 X-Sequence-Number: 990 On Sun, 2003-01-26 at 15:24, Medve Gabor wrote: > Hi all, > > 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. I think you can only do LIKE queries on CHAR-type fields. BETWEEN ought to help you, though: SELECT * FROM foo where prim_key BETWEEN YYYYMMDD00000000 and YYYYMMDD999999999; Alternatively, if you really want to do 'YYYYMMDD%', you could create a functional index on to_char(prim_key). Lastly, you could create 2 fields and create a compound PK: PK_DATE DATE, PK_SERIAL BIGINT Then you could say: SELECT * FROM foo where pk_date = 'YYYY-MM-DD' Of course, then you'd be adding an extra 8 bytes to each column... -- +---------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "Fear the Penguin!!" | +---------------------------------------------------------------+