X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B55F5D1B8BD for ; Wed, 28 Apr 2004 05:28:08 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 43467-06 for ; Wed, 28 Apr 2004 05:28:08 -0300 (ADT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 79120D1B8B1 for ; Wed, 28 Apr 2004 05:28:06 -0300 (ADT) Received: (qmail 8577 invoked by uid 500); 28 Apr 2004 08:32:23 -0000 Date: Wed, 28 Apr 2004 03:32:23 -0500 From: Bruno Wolff III To: Edoardo Ceccarelli Cc: Christopher Kings-Lynne , pg@fastcrypt.com, "pgsql-jdbc@postgresql.org" , pgsql-performance@postgresql.org, pgsql-admin@postgresql.org Subject: Re: [JDBC] [PERFORM] is a good practice to create an index on the Message-ID: <20040428083223.GA8384@wolff.to> Mail-Followup-To: Edoardo Ceccarelli , Christopher Kings-Lynne , pg@fastcrypt.com, "pgsql-jdbc@postgresql.org" , pgsql-performance@postgresql.org, pgsql-admin@postgresql.org References: <408D3AE8.5020006@axa.it> <408DC9E1.8020704@familyhealth.com.au> <1083067187.14759.178.camel@localhost.localdomain> <408E75C2.5060703@familyhealth.com.au> <1083079114.14759.204.camel@localhost.localdomain> <408ED3E2.802@axa.it> <408F0944.5060608@familyhealth.com.au> <408F679A.9030608@axa.it> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <408F679A.9030608@axa.it> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/332 X-Sequence-Number: 13305 On Wed, Apr 28, 2004 at 10:13:14 +0200, Edoardo Ceccarelli wrote: > do you mean that, declaring an index serial, I'd never have to deal with > incrementing its primary key? good to know! That isn't what is happening. Serial is a special type. It is int plus a default rule linked to a sequence. No index is created by default for the serial type. Declaring a column as a primary key will however create a unique index on that column. Also note that you should only assume that the serial values are unique. (This assumes that you don't use setval and that you don't roll a sequence over.) Within a single session you can assume the sequence values will be monotonicly increasing. The values that end up in your table can have gaps. Typically this happens when a transaction rolls back after obtaining a new value from a sequence. It can also happen if you grab sequence values in larger blocks (which might be more efficient if a session normally acquires mulitple values from a particular sequence) than the default 1. > anyway in this particular situation I don't need such accurate > behaviour: this table is filled up with a lot of data twice per week and > it's used only to answer queries. > I could drop it whenever I want :) You really don't want to use oids.