Indexing foreign keys

Yes, I had not only done a "vacuum full analyze" on the PG db once I
stuffed it, but I also compared that with an IFX db that I had run
"update statistics high" on. Things are much better with the FK indexes.

Did the docs say to index those FK fields (is that standard in the DB
industry?), or was I just spoiled by IFX doing it for me? ;)

Thanks!

Chad Thompson wrote:

Make sure that you've run a vacuum and an analyze. There is also a
performance hit if the types of the fields or values are different. ie int
to int8

--
Matt Mello

Matt,

Did the docs say to index those FK fields (is that standard in the DB
industry?), or was I just spoiled by IFX doing it for me? ;)

It's pretty standard in the DB industry.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

On Mon, 2003-01-27 at 14:39, Matt Mello wrote:

Due to reasons that everyone can probably intuit, we are porting a large
server application from IBM Informix to PG. However, things that take
milliseconds in IFX are taking HOURS (not joking) in PG. I *think* I
may have come across some reasons why, but I would like to see if anyone
else has an opinion. I could not find anything relevant in docs (but if
it is there, please point me to it).

Let me give an example of one of the problems...

I have a table that utilizes 2 foreign keys. It has 400000 records of
approximately 512 bytes each (mostly text, except for the keys). When I
run a specific query on it, it takes 8000ms to complete, and it always
does a full scan.

I "assumed" that since I did not have to create an index on those
foreign key fields in IFX, that I did not have to in PG. However, just
for kicks, I created an index on those 2 fields, and my query time
(after the first, longer attempt, which I presume is from loading an
index) went from 8000ms to 100ms.

So, do we ALWAYS have to create indexes for foreign key fields in PG?
Do the docs say this? (I couldn't find the info.)

When you say "I created an index on those 2 fields", so you mean on
the fields in the 400K row table, or on the keys in the "fact tables"
that the 400K row table?

Also, in IFX, could the creation of the foreign indexes have implicitly
created indexes?
The reason I ask is that this is what happens in Pg when you create a
PK.

-- 
+---------------------------------------------------------------+
| Ron Johnson, Jr.        mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA      http://members.cox.net/ron.l.johnson  |
|                                                               |
| "Fear the Penguin!!"                                          |
+---------------------------------------------------------------+

On Mon, 27 Jan 2003, Matt Mello wrote:

Due to reasons that everyone can probably intuit, we are porting a large
server application from IBM Informix to PG. However, things that take
milliseconds in IFX are taking HOURS (not joking) in PG. I *think* I
may have come across some reasons why, but I would like to see if anyone
else has an opinion. I could not find anything relevant in docs (but if
it is there, please point me to it).

Let me give an example of one of the problems...

I have a table that utilizes 2 foreign keys. It has 400000 records of
approximately 512 bytes each (mostly text, except for the keys). When I
run a specific query on it, it takes 8000ms to complete, and it always
does a full scan.

I "assumed" that since I did not have to create an index on those
foreign key fields in IFX, that I did not have to in PG. However, just
for kicks, I created an index on those 2 fields, and my query time
(after the first, longer attempt, which I presume is from loading an
index) went from 8000ms to 100ms.

So, do we ALWAYS have to create indexes for foreign key fields in PG?
Do the docs say this? (I couldn't find the info.)

You don't always need to create them, because there are fk patterns where
an index is counterproductive, but if you're not in one of those cases you
should create them. I'm not sure the docs actually say anything about
this however.

Guys,

You don't always need to create them, because there are fk patterns where
an index is counterproductive, but if you're not in one of those cases you
should create them. I'm not sure the docs actually say anything about
this however.

See:
http://techdocs.postgresql.org/techdocs/pgsqladventuresep2.php
http://techdocs.postgresql.org/techdocs/pgsqladventuresep3.php

(and yes, I know I need to finish this series ...)

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:

Matt,

Did the docs say to index those FK fields (is that standard in the DB
industry?), or was I just spoiled by IFX doing it for me? ;)

It's pretty standard in the DB industry.

I didn't know that, but I'm new to the DB field. I've gleaned quite a
few tips from this group, especially from responses to people with slow
queries/databases, but this is the first I've noticed it this tip. I'll
try it on my db too.

--
Ron St.Pierre
Syscor R&D
tel: 250-361-1681
email: rstpierre@syscor.com

Ron Johnson wrote:

When you say "I created an index on those 2 fields", so you mean on
the fields in the 400K row table, or on the keys in the "fact tables"
that the 400K row table?

Also, in IFX, could the creation of the foreign indexes have implicitly
created indexes?
The reason I ask is that this is what happens in Pg when you create a
PK.

The 400K row table has 2 fields that are FK fields. The already-indexed
PK fields that they reference are in another table. I just recently
added indexes to the 2 FK fields in the 400K row table to get the speed
boost.

Yes. In IFX, when you create a FK, it seems to create indexes
automatically for you, just like PG does with PK's.

In fact, I can't imagine a situation where you would NOT want a FK
indexed. I guess there must be one, or else I'm sure the developers
would have already added auto-creation of indexes to the FK creation, as
well.

--
Matt Mello

You don't always need to create them, because there are fk patterns where
an index is counterproductive, but if you're not in one of those cases you
should create them. I'm not sure the docs actually say anything about
this however.

I would try to add a comment about this to the interactive docs if they
weren't so far behind already (7.2.1). :\

--
Matt Mello

On Mon, 27 Jan 2003, Matt Mello wrote:

Yes. In IFX, when you create a FK, it seems to create indexes
automatically for you, just like PG does with PK's.

In fact, I can't imagine a situation where you would NOT want a FK
indexed. I guess there must be one, or else I'm sure the developers
would have already added auto-creation of indexes to the FK creation, as
well.

Any case where the pk table is small enough and the values are fairly
evenly distributed so that the index isn't very selective. You end up not
using the index anyway because it's not selective and you pay the costs
involved in keeping it up to date.

On Mon, 2003-01-27 at 23:46, Matt Mello wrote:

Ron Johnson wrote:

When you say "I created an index on those 2 fields", so you mean on
the fields in the 400K row table, or on the keys in the "fact tables"
that the 400K row table?

Also, in IFX, could the creation of the foreign indexes have implicitly
created indexes?
The reason I ask is that this is what happens in Pg when you create a
PK.

The 400K row table has 2 fields that are FK fields. The already-indexed
PK fields that they reference are in another table. I just recently
added indexes to the 2 FK fields in the 400K row table to get the speed
boost.

Yes. In IFX, when you create a FK, it seems to create indexes
automatically for you, just like PG does with PK's.

In fact, I can't imagine a situation where you would NOT want a FK
indexed. I guess there must be one, or else I'm sure the developers
would have already added auto-creation of indexes to the FK creation, as
well.

When I took my brain out of 1st gear, it was "Doh!": I realized that
I was thinking backwards...

-- 
+---------------------------------------------------------------+
| Ron Johnson, Jr.        mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA      http://members.cox.net/ron.l.johnson  |
|                                                               |
| "Fear the Penguin!!"                                          |
+---------------------------------------------------------------+

In fact, I can't imagine a situation where you would NOT want a FK
indexed. I guess there must be one, or else I'm sure the developers
would have already added auto-creation of indexes to the FK creation, as
well.

Any case where the pk table is small enough and the values are fairly
evenly distributed so that the index isn't very selective. You end up not
using the index anyway because it's not selective and you pay the costs
involved in keeping it up to date.

Or you want an index on two or more fields that includes the FK as the
primary field. No sense in making two indexes.

Greg