Re: indexes on primary and foreign keys

От: Michael Fuhr
Тема: Re: indexes on primary and foreign keys
Дата: ,
Msg-id: 20060111232157.GA88829@winnie.fuhr.org
(см: обсуждение, исходный текст)
Ответ на: indexes on primary and foreign keys  (Burak Seydioglu)
Ответы: Re: indexes on primary and foreign keys  (Burak Seydioglu)
Re: indexes on primary and foreign keys  (K C Lau)
Список: pgsql-performance

Скрыть дерево обсуждения

indexes on primary and foreign keys  (Burak Seydioglu, )
 Re: indexes on primary and foreign keys  (Tom Lane, )
 Re: indexes on primary and foreign keys  (Michael Fuhr, )
  Re: indexes on primary and foreign keys  (Burak Seydioglu, )
  Re: indexes on primary and foreign keys  (K C Lau, )
   Re: indexes on primary and foreign keys  (Michael Glaesemann, )
    Re: indexes on primary and foreign keys  (Michael Fuhr, )
    Re: indexes on primary and foreign keys  (K C Lau, )
     Re: indexes on primary and foreign keys  (Tom Lane, )

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


В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: indexes on primary and foreign keys
От: Simon Riggs
Дата:
Сообщение: Re: Extremely irregular query performance