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 5F7239DCA25 for ; Wed, 11 Jan 2006 19:52:40 -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 21156-03 for ; Wed, 11 Jan 2006 19:52:42 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.194]) by postgresql.org (Postfix) with ESMTP id 290099DCA17 for ; Wed, 11 Jan 2006 19:52:36 -0400 (AST) Received: by zproxy.gmail.com with SMTP id i11so270787nzh for ; Wed, 11 Jan 2006 15:52:40 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=j6eY+gI6xI+YASJhDBITeOfXkSTBrMdoGYLB7bY1AXqIQi0E//MqZ18Hcy9/tgpu8jLyQgq5Dv1fgGoDLvZfNOF2b4Xe7ywduPYpwsbzrjKJ/OEmv/ApUN9cBqNzMzQeeiSJha/QkHrsZ5Ic5+xKFRG+U2NCCUqjz/FxiIWNyZ4= Received: by 10.65.107.11 with SMTP id j11mr460577qbm; Wed, 11 Jan 2006 15:52:40 -0800 (PST) Received: by 10.65.156.18 with HTTP; Wed, 11 Jan 2006 15:52:40 -0800 (PST) Message-ID: <1b8a973c0601111552w31fab815kfac989e1782de98d@mail.gmail.com> Date: Wed, 11 Jan 2006 15:52:40 -0800 From: Burak Seydioglu To: Michael Fuhr Subject: Re: indexes on primary and foreign keys Cc: pgsql-performance@postgresql.org In-Reply-To: <20060111232157.GA88829@winnie.fuhr.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11920_17810882.1137023560071" References: <1b8a973c0601111438m3e0b1cc0hf9c387a4a1d6791d@mail.gmail.com> <20060111232157.GA88829@winnie.fuhr.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.473 required=5 tests=[AWL=0.472, HTML_MESSAGE=0.001] X-Spam-Score: 0.473 X-Spam-Level: X-Archive-Number: 200601/138 X-Sequence-Number: 16616 ------=_Part_11920_17810882.1137023560071 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline How about the performance effect on SELECT statements joining multiple tables (LEFT JOINS)? I have been reading all day and here is an excerpt from one article that is located at http://pgsql.designmagick.com/tutorial.php?id=3D19&pid=3D28 [quote] The best reason to use an index is for joining multiple tables together in a single query. When two tables are joined, a record that exists in both tables needs to be used to link them together. If possible, the column in both tables should be indexed. [/quote] Regarding similar posts, I tried to search the archives but for some reason the search utility is not functioning. http://search.postgresql.org/archives.search?cs=3Dutf-8&fm=3Don&st=3D20&dt= =3Dback&q=3Dindex Thank you very much for your help. Burak On 1/11/06, Michael Fuhr wrote: > > On Wed, Jan 11, 2006 at 02:38:42PM -0800, Burak Seydioglu wrote: > > 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 (i= n > > 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); > > PostgreSQL automatically creates indexes on primary keys. If you run > the above CREATE TABLE statement in psql you should see a message to > that effect: > > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "cities_pkey" for table "cities" > > If you look at the table definition you should see the primary > key's index: > > test=3D> \d cities > Table "public.cities" > Column | Type | Modifiers > -----------+-----------------------+----------- > city_id | integer | not null > city_name | character varying(50) | > Indexes: > "cities_pkey" PRIMARY KEY, btree (city_id) > > So you don't need to create another index on cities.city_id. However, > PostgreSQL doesn't automatically create an index on the referring > column of a foreign key constraint, so if you have another table like > > CREATE TABLE districts ( > district_id integer PRIMARY KEY, > district_name varchar(50), > city_id integer REFERENCES cities > ); > > then you won't automatically get an index on districts.city_id. > It's generally a good idea to create one; failure to do so can cause > deletes and updates on the referred-to table (cities) to be slow > because referential integrity checks would have to do sequential > scans on the referring table (districts). Indeed, performance > problems for exactly this reason occasionally come up in the mailing > lists. > > -- > Michael Fuhr > ------=_Part_11920_17810882.1137023560071 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline How about the performance effect on SELECT statements joining multiple tabl= es (LEFT JOINS)?

I have been reading all day and here is an excerpt from one article that is located at http://pgsql.designmagick.com/tutorial.php?id=3D19&pid=3D28

[quote]
The best reason to use an ind=
ex is for joining multiple tables together in a single query. When two tabl=
es are joined, a record
that exists in both tables needs to be used to l=
ink them together. If possible, the column in both tables should be indexed=
.
[/quote]

Regarding similar posts, I tried to search the archives but for some reason= the search utility is not functioning.
http://search.postgresql.org= /archives.search?cs=3Dutf-8&fm=3Don&st=3D20&dt=3Dback&q=3Di= ndex

Thank you very much for your help.

Burak


On 1/11/06, Michael Fuhr <mike@fuhr.org= > wrote:
On Wed, Jan 11, 2006 at 02:38:42PM -0800, Burak Seydioglu wrote:
> 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 belo= w?
>
> CREATE TABLE cities (
>   city_id intege= r primary key,
>   city_name varchar(50)
> );
>
> CREAT= E INDEX city_id_index ON cities(city_id);

PostgreSQL automatically c= reates indexes on primary keys.  If you run
the above CREATE T= ABLE statement in psql you should see a message to
that effect:

NOTICE:  CREATE TABLE / PRIMARY KEY will = create implicit index "cities_pkey" for table "cities"<= br>
If you look at the table definition you should see the primary
ke= y's index:

test=3D> \d cities
       =       Table "public.cities"
 &nb= sp;Column   |         Type          | Modifiers
-----------+-----------------------+-----------
city_id&nb= sp;  | integer         &n= bsp;     | not null
city_name | character varying(5= 0) |
Indexes:
    "cities_pkey" PRIMARY= KEY, btree (city_id)

So you don't need to create another index on cities.city_id. &= nbsp;However,
PostgreSQL doesn't automatically create an index on the re= ferring
column of a foreign key constraint, so if you have another table= like

CREATE TABLE districts (
  district_id   &n= bsp;integer PRIMARY KEY,
  district_name  varchar(50= ),
  city_id        in= teger REFERENCES cities
);

then you won't automatically get an in= dex on districts.city_id .
It's generally a good idea to create one; failure to do so can causedeletes and updates on the referred-to table (cities) to be slow
becau= se referential integrity checks would have to do sequential
scans on the= referring table (districts).  Indeed, performance
problems for exactly this reason occasionally come up in the mailinglists.

--
Michael Fuhr

------=_Part_11920_17810882.1137023560071--