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 DA0219DC863 for ; Wed, 11 Jan 2006 18:38:43 -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 03991-10 for ; Wed, 11 Jan 2006 18:38:45 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.197]) by postgresql.org (Postfix) with ESMTP id 790AF9DC839 for ; Wed, 11 Jan 2006 18:38:41 -0400 (AST) Received: by zproxy.gmail.com with SMTP id i11so260334nzh for ; Wed, 11 Jan 2006 14:38:44 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=JCU0onSkWjodDjUrTKgEqL9FTHL0cYBiOdfjXlqVu55QFXsd4eha/oNsWWShFnFzEhxs3rWpSKp6KDMhK19w1NS91EWjwAAivfklUnApQ/8FoY4O+KuspOsEP60RmC9ldgz/C9m2ij/gN6iSFveUJdPd6E2WEQXJ/4Bqgup845k= Received: by 10.65.194.17 with SMTP id w17mr467230qbp; Wed, 11 Jan 2006 14:38:43 -0800 (PST) Received: by 10.65.156.18 with HTTP; Wed, 11 Jan 2006 14:38:42 -0800 (PST) Message-ID: <1b8a973c0601111438m3e0b1cc0hf9c387a4a1d6791d@mail.gmail.com> Date: Wed, 11 Jan 2006 14:38:42 -0800 From: Burak Seydioglu To: pgsql-performance@postgresql.org Subject: indexes on primary and foreign keys MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11166_32945758.1137019122582" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.946 required=5 tests=[HTML_10_20=0.945, HTML_MESSAGE=0.001] X-Spam-Score: 0.946 X-Spam-Level: X-Archive-Number: 200601/131 X-Sequence-Number: 16609 ------=_Part_11166_32945758.1137019122582 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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? CREATE TABLE cities ( city_id integer primary key, city_name varchar(50) ); CREATE INDEX city_id_index ON cities(city_id); Thanks for any insight. Burak ------=_Part_11166_32945758.1137019122582 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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?

CREATE TABLE cities (
ci= ty_id integer primary key,
city_name varchar(50)
);

CREATE I= NDEX city_id_index ON cities(city_id);

Thanks for any insight.

Burak

------=_Part_11166_32945758.1137019122582--