Thread: Indexing foreign keys
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
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!!" | +---------------------------------------------------------------+