Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id A52D9B5DBD6 for ; Tue, 11 Oct 2011 20:11:59 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 08481-04 for ; Tue, 11 Oct 2011 23:11:51 +0000 (UTC) X-Greylist: delayed 07:55:25.412075 by SQLgrey-1.8.0-rc2 Received: from toroondcbmts01-srv.bellnexxia.net (toroondcbmts01-srv.bellnexxia.net [207.236.237.35]) by mail.postgresql.org (Postfix) with ESMTP id A63DFB5DBCA for ; Tue, 11 Oct 2011 20:11:51 -0300 (ADT) Received: from toip56-bus.srvr.bell.ca ([67.69.240.142]) by toroondcbmts08-srv.bellnexxia.net (InterMail vM.8.00.01.00 201-2244-105-20090324) with ESMTP id <20111011151624.HSIT10561.toroondcbmts08-srv.bellnexxia.net@toip56-bus.srvr.bell.ca> for ; Tue, 11 Oct 2011 11:16:24 -0400 Received: from toip35-bus.srvr.bell.ca ([67.69.240.36]) by toip56-bus.srvr.bell.ca with ESMTP; 11 Oct 2011 11:16:07 -0400 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AjgHAINUlE5GMVId/2dsb2JhbABDgk2Weo5hgQWBWgh+BQYQFD4aJQEEGwOhIqABhmthBJk8jCc Received: from unknown (HELO CAPRICA) ([70.49.82.29]) by toip35-bus.srvr.bell.ca with ESMTP; 11 Oct 2011 11:16:06 -0400 From: "Carlo Stonebanks" To: Subject: Composite keys Date: Tue, 11 Oct 2011 11:16:07 -0400 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_04F5_01CC8807.2C70CB80" X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcyIKIaYfyaTTiLWSheb3WbqR+NB3w== X-MimeOLE: Produced By Microsoft MimeOLE V6.1.7601.17609 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201110/122 X-Sequence-Number: 45344 This is a multi-part message in MIME format. ------=_NextPart_000_04F5_01CC8807.2C70CB80 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Excuse the noob question, I couldn't find any reading material on this topic. Let's say my_table has two fields, pkey_id and another_id. The primary key is pkey_id and of course indexed. Then someone adds a composite index on btree(pkey_id, another_id). Question 1) Is there any benefit to having pkey_id in the second index (assuming the index was created to satisfy some arbitrary WHERE clause)? Question 2) Regardless of the answer to Question 1 - if another_id is not guaranteed to be unique, whereas pkey_id is - there any value to changing the order of declaration (more generally, is there a performance impact for column ordering in btree composite keys?) Thanks, Carlo ------=_NextPart_000_04F5_01CC8807.2C70CB80 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Excuse the noob question, I couldn’t = find any reading material on this topic.

Let’s say my_table has two fields, = pkey_id and another_id. The primary key is pkey_id and of course = indexed.

Then someone adds a composite index on = btree(pkey_id, another_id).

Question 1) Is there any benefit to having = pkey_id in the second index (assuming the index was created to satisfy some arbitrary = WHERE clause)?

Question 2) Regardless of the answer to = Question 1 - if another_id is not guaranteed to be unique, whereas pkey_id is – = there any value to changing the order of declaration (more generally, is there a performance impact for column ordering in btree composite = keys?)

Thanks,

Carlo

------=_NextPart_000_04F5_01CC8807.2C70CB80--