From: Burak Seydioglu <buraks78@gmail.com>
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: indexes on primary and foreign keys
Date: Wed, 11 Jan 2006 15:52:40 -0800
Message-ID: <1b8a973c0601111552w31fab815kfac989e1782de98d@mail.gmail.com> (raw)
In-Reply-To: <20060111232157.GA88829@winnie.fuhr.org>
References: <1b8a973c0601111438m3e0b1cc0hf9c387a4a1d6791d@mail.gmail.com>
<20060111232157.GA88829@winnie.fuhr.org>
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=19&pid=28
[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=utf-8&fm=on&st=20&dt=back&q=index
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 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=> \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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: buraks78@gmail.com, mike@fuhr.org
Subject: Re: indexes on primary and foreign keys
In-Reply-To: <1b8a973c0601111552w31fab815kfac989e1782de98d@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox