Re: indexes on primary and foreign keys

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(at)fuhr(dot)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
>