X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 1C6DA9DC833 for ; Wed, 11 Jan 2006 19:06:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 10138-10 for ; Wed, 11 Jan 2006 19:06:36 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 00D639DC804 for ; Wed, 11 Jan 2006 19:06:31 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k0BN6XSH029043; Wed, 11 Jan 2006 18:06:33 -0500 (EST) To: Burak Seydioglu cc: pgsql-performance@postgresql.org Subject: Re: indexes on primary and foreign keys In-reply-to: <1b8a973c0601111438m3e0b1cc0hf9c387a4a1d6791d@mail.gmail.com> References: <1b8a973c0601111438m3e0b1cc0hf9c387a4a1d6791d@mail.gmail.com> Comments: In-reply-to Burak Seydioglu message dated "Wed, 11 Jan 2006 14:38:42 -0800" Date: Wed, 11 Jan 2006 18:06:33 -0500 Message-ID: <29042.1137020793@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.222 required=5 tests=[AWL=-0.054, MAILTO_TO_SPAM_ADDR=0.276] X-Spam-Score: 0.222 X-Spam-Level: X-Archive-Number: 200601/133 X-Sequence-Number: 16611 Burak Seydioglu writes: > I do a load of sql joins using primary and foreign keys. What i would like > to know if PostgreSQL creates indexes on these columns automatically (in > addition to using them to maintain referential integrity) or do I have to > create an index manually on these columns as indicated below? Indexes are only automatically created where needed to enforce a UNIQUE constraint. That includes primary keys, but not foreign keys. Note that you only really need an index on the referencing (non-unique) side of a foreign key if you are worried about performance of DELETEs or key changes on the referenced table. If you seldom or never do that, you might want to dispense with the index. regards, tom lane