pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Indexing foreign keys
12+ messages / 6 participants
[nested] [flat]

* Indexing foreign keys
@ 2003-01-27 20:39  Matt Mello <alien@spaceship.com>
  0 siblings, 3 replies; 12+ messages in thread

From: Matt Mello @ 2003-01-27 20:39 UTC (permalink / raw)
  To: pgsql-performance

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.)

I will create other threads for my other issues.

Thanks!

-- 
Matt Mello




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Indexing foreign keys
@ 2003-01-27 20:56  Matt Mello <alien@spaceship.com>
  parent: Matt Mello <alien@spaceship.com>
  2 siblings, 1 reply; 12+ messages in thread

From: Matt Mello @ 2003-01-27 20:56 UTC (permalink / raw)
  To: pgsql-performance

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





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Indexing foreign keys
@ 2003-01-27 21:12  Josh Berkus <josh@agliodbs.com>
  parent: Matt Mello <alien@spaceship.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Josh Berkus @ 2003-01-27 21:12 UTC (permalink / raw)
  To: Matt Mello <alien@spaceship.com>; pgsql-performance


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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Indexing foreign keys
@ 2003-01-27 21:13  Ron Johnson <ron.l.johnson@cox.net>
  parent: Matt Mello <alien@spaceship.com>
  2 siblings, 1 reply; 12+ messages in thread

From: Ron Johnson @ 2003-01-27 21:13 UTC (permalink / raw)
  To: pgsql-performance

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!!"                                          |
+---------------------------------------------------------------+




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Indexing foreign keys
@ 2003-01-27 21:16  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Matt Mello <alien@spaceship.com>
  2 siblings, 2 replies; 12+ messages in thread

From: Stephan Szabo @ 2003-01-27 21:16 UTC (permalink / raw)
  To: Matt Mello <alien@spaceship.com>; +Cc: pgsql-performance

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.






^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Indexing foreign keys
@ 2003-01-27 21:23  Josh Berkus <josh@agliodbs.com>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 12+ messages in thread

From: Josh Berkus @ 2003-01-27 21:23 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Matt Mello <alien@spaceship.com>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Indexing foreign keys
@ 2003-01-28 00:06  Ron St.Pierre <rstpierre@syscor.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Ron St.Pierre @ 2003-01-28 00:06 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Indexing foreign keys
@ 2003-01-28 05:46  Matt Mello <alien@spaceship.com>
  parent: Ron Johnson <ron.l.johnson@cox.net>
  0 siblings, 2 replies; 12+ messages in thread

From: Matt Mello @ 2003-01-28 05:46 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Indexing foreign keys
@ 2003-01-28 05:51  Matt Mello <alien@spaceship.com>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 12+ messages in thread

From: Matt Mello @ 2003-01-28 05:51 UTC (permalink / raw)
  To: pgsql-performance

> 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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Indexing foreign keys
@ 2003-01-28 07:49  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Matt Mello <alien@spaceship.com>
  1 sibling, 1 reply; 12+ messages in thread

From: Stephan Szabo @ 2003-01-28 07:49 UTC (permalink / raw)
  To: Matt Mello <alien@spaceship.com>; +Cc: pgsql-performance

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.




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Indexing foreign keys
@ 2003-01-28 15:15  Ron Johnson <ron.l.johnson@cox.net>
  parent: Matt Mello <alien@spaceship.com>
  1 sibling, 0 replies; 12+ messages in thread

From: Ron Johnson @ 2003-01-28 15:15 UTC (permalink / raw)
  To: pgsql-performance

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!!"                                          |
+---------------------------------------------------------------+




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [PERFORM] Indexing foreign keys
@ 2003-01-28 21:23  Gregory Wood <gregw@com-stock.com>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Gregory Wood @ 2003-01-28 21:23 UTC (permalink / raw)
  To: PostgreSQL-General <pgsql-general@postgresql.org>

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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2003-01-28 21:23 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-27 20:39 Indexing foreign keys Matt Mello <alien@spaceship.com>
2003-01-27 20:56 ` Matt Mello <alien@spaceship.com>
2003-01-27 21:12   ` Josh Berkus <josh@agliodbs.com>
2003-01-28 00:06     ` Ron St.Pierre <rstpierre@syscor.com>
2003-01-27 21:13 ` Ron Johnson <ron.l.johnson@cox.net>
2003-01-28 05:46   ` Matt Mello <alien@spaceship.com>
2003-01-28 07:49     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-28 21:23       ` Gregory Wood <gregw@com-stock.com>
2003-01-28 15:15     ` Ron Johnson <ron.l.johnson@cox.net>
2003-01-27 21:16 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-27 21:23   ` Josh Berkus <josh@agliodbs.com>
2003-01-28 05:51   ` Matt Mello <alien@spaceship.com>

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