Re: indexes on primary and foreign keys

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

Burak Seydioglu <buraks78(at)gmail(dot)com> writes:
> 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?

Indexes are only automatically created where needed to enforce a UNIQUE
constraint. That includes primary keys, but not foreign keys.

Note that you only really need an index on the referencing (non-unique)
side of a foreign key if you are worried about performance of DELETEs
or key changes on the referenced table. If you seldom or never do that,
you might want to dispense with the index.

regards, 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

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
>

At 07:21 06/01/12, 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 (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"

Is there a way to suppress this notice when I create tables in a script?

Best regards,
KC.

On Jan 12, 2006, at 9:36 , K C Lau wrote:

>> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
>> "cities_pkey" for table "cities"
>
> Is there a way to suppress this notice when I create tables in a
> script?

Set[1] your log_min_messages to WARNING or higher[2].

[1](http://www.postgresql.org/docs/current/interactive/sql-set.html)
[2](http://www.postgresql.org/docs/current/interactive/runtime-config-
logging.html#RUNTIME-CONFIG-LOGGING-WHEN)

Michael Glaesemann
grzm myrealbox com

On Thu, Jan 12, 2006 at 10:26:58AM +0900, Michael Glaesemann wrote:
> On Jan 12, 2006, at 9:36 , K C Lau wrote:
> >>NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> >>"cities_pkey" for table "cities"
> >
> >Is there a way to suppress this notice when I create tables in a
> >script?
>
> Set[1] your log_min_messages to WARNING or higher[2].

Or client_min_messages, depending on where you don't want to see
the notice.

--
Michael Fuhr

At 09:26 06/01/12, you wrote:

>On Jan 12, 2006, at 9:36 , K C Lau wrote:
>
>>>NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
>>>"cities_pkey" for table "cities"
>>
>>Is there a way to suppress this notice when I create tables in a
>>script?
>
>Set[1] your log_min_messages to WARNING or higher[2].
>
>[1](http://www.postgresql.org/docs/current/interactive/sql-set.html)
>[2](http://www.postgresql.org/docs/current/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN)
>
>Michael Glaesemann
>grzm myrealbox com

Thanks. The side effect is that it would suppress other notices which might
be useful.

I was looking for a way to suppress the notice within the CREATE TABLE
statement but could not.
I noticed that when I specify a constraint name for the primary key, it
would create an implicit index with the constraint name. So may be if the
optional constraint name is specified by the user, then the notice can be
suppressed. Indeed the manual already says that the index will be
automatically created.

BTW, there's an extra space in link[2] above which I have removed.

Best regards,
KC.

K C Lau <kclau60(at)netvigator(dot)com> writes:
> Thanks. The side effect is that it would suppress other notices which might
> be useful.

There's been some discussion of subdividing the present "notice"
category into two subclasses, roughly defined as "only novices wouldn't
know this" and "maybe this is interesting". What's missing at this
point is a concrete proposal as to which existing NOTICE messages should
go into each category. If you feel like tackling the project, go for it...

regards, tom lane