Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 32B121337B80 for ; Tue, 2 Nov 2010 08:53:36 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 48681-04 for ; Tue, 2 Nov 2010 11:53:30 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from depesz.com (martin.depesz.com [188.40.39.14]) by mail.postgresql.org (Postfix) with ESMTP id B7CD01337B99 for ; Tue, 2 Nov 2010 08:53:28 -0300 (ADT) Received: from martin.depesz.com ([188.40.39.14] helo=depesz.com) by depesz.com with esmtpa (Exim 4.69) (envelope-from ) id 1PDFQd-0001S8-4M; Tue, 02 Nov 2010 12:53:27 +0100 Date: Tue, 2 Nov 2010 12:53:27 +0100 From: hubert depesz lubaczewski To: =?utf-8?Q?C=C3=A9dric?= Villemain Cc: Divakar Singh , pgsql-performance@postgresql.org Subject: Re: Insert performance with composite index Message-ID: <20101102115327.GA5269@depesz.com> Reply-To: depesz@depesz.com References: <293254.80548.qm@web65413.mail.ac4.yahoo.com> <20101102094539.GA6686@depesz.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.5.21 (2010-09-15) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/16 X-Sequence-Number: 41040 On Tue, Nov 02, 2010 at 12:04:42PM +0100, Cédric Villemain wrote: > 2010/11/2 hubert depesz lubaczewski : > > On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote: > >> >   CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex) > >> > ) > >> > the index definition is > >> > CREATE INDEX "PK_AT2" > >> >   ON ABC > >> >   USING btree > >> >   (event, tableindex) > >> > TABLESPACE sample; > >> > >> Indexing twice the same column is useless. (perhaps move your PK to > >> the tablespace 'sample' is good too ?) > > > > why do you say that? > > these are not the same indexes and they serve different purposes. > > Given that tableindex is the PK column, I really like to now the usage > pattern for having it indexed twice. select * from table where event = 123 order by tableindex desc limit 50; Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007