Thread: contrib idea
Hi All, You know how when you create a foreign key in postgres it isn't automatically indexed, and it seems to me that it's very useful to have indexed foreign keys, especially if you use lots of them. So, how about a 'findslowfks' contrib? This would basically be similar to Bruce's findoidjoins thingy... Just an idea, Chris
> Hi All, > > You know how when you create a foreign key in postgres it isn't > automatically indexed, and it seems to me that it's very useful to have > indexed foreign keys, especially if you use lots of them. > > So, how about a 'findslowfks' contrib? This would basically be similar to > Bruce's findoidjoins thingy... Why would you want an index on a foreign key. Primary I can understand, but is there use to foreignt? Is it for checking of changes to primary keys? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> If you have a foreign key on a column, then whenever the primary key is > modified, the following checks may occur: > > * Check to see if the child row exists (no action) > * Delete the child row (cascade delete) > * Update the child row (cascade update) > > All of which will benefit from an index... OK, then perhaps we should be creating an index automatically? Folks? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
If you have a foreign key on a column, then whenever the primary key is modified, the following checks may occur: * Check to see if the child row exists (no action) * Delete the child row (cascade delete) * Update the child row (cascade update) All of which will benefit from an index... Chris > -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > Sent: Friday, 21 December 2001 11:59 AM > To: Christopher Kings-Lynne > Cc: Hackers > Subject: Re: [HACKERS] contrib idea > > > > Hi All, > > > > You know how when you create a foreign key in postgres it isn't > > automatically indexed, and it seems to me that it's very useful to have > > indexed foreign keys, especially if you use lots of them. > > > > So, how about a 'findslowfks' contrib? This would basically be > similar to > > Bruce's findoidjoins thingy... > > Why would you want an index on a foreign key. Primary I can understand, > but is there use to foreignt? Is it for checking of changes to primary > keys? > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 >
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> If you have a foreign key on a column, then whenever the primary key is
>> modified, the following checks may occur:
>>
>> * Check to see if the child row exists (no action)
>> * Delete the child row (cascade delete)
>> * Update the child row (cascade update)
>>
>> All of which will benefit from an index...
> OK, then perhaps we should be creating an index automatically? Folks?
We should not *force* people to have an index. If the master table very
seldom changes, then an index on the referencing table will be a net
loss (at least as far as the foreign-key ops go). You'll pay for it on
every referencing-table update, and use it only seldom.
Possibly there should be an entry in the "performance tips" chapter
recommending that people consider adding an index on the referencing
column if they are concerned about the speed of updates to the
referenced table. But I dislike software that considers itself smarter
than the DBA.
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> If you have a foreign key on a column, then whenever the primary key is > >> modified, the following checks may occur: > >> > >> * Check to see if the child row exists (no action) > >> * Delete the child row (cascade delete) > >> * Update the child row (cascade update) > >> > >> All of which will benefit from an index... > > > OK, then perhaps we should be creating an index automatically? Folks? > > We should not *force* people to have an index. If the master table very > seldom changes, then an index on the referencing table will be a net > loss (at least as far as the foreign-key ops go). You'll pay for it on > every referencing-table update, and use it only seldom. > > Possibly there should be an entry in the "performance tips" chapter > recommending that people consider adding an index on the referencing > column if they are concerned about the speed of updates to the > referenced table. But I dislike software that considers itself smarter > than the DBA. Keep in mind that the penalty for no index is a sequential scan, which _usually_ is a light operation. In fact, many queryes don't even use indexes if they are going to need to see more than a small portion of the table. But yes, if your primary key is changing often, that is a valid issue. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Possibly there should be an entry in the "performance tips" chapter > recommending that people consider adding an index on the referencing > column if they are concerned about the speed of updates to the > referenced table. But I dislike software that considers itself smarter > than the DBA. Which is why I proposed a contrib that can assist the DBA in finding ones they've forgotten to index... In fact, it would be cool if it just dumped out a whole bunch of CREATE INDEX commands... Chris
> > If you have a foreign key on a column, then whenever the primary key is > > modified, the following checks may occur: > > > > * Check to see if the child row exists (no action) > > * Delete the child row (cascade delete) > > * Update the child row (cascade update) > > > > All of which will benefit from an index... > > OK, then perhaps we should be creating an index automatically? Folks? The index is only useful where you actually have an on delete or on update clause. I don't think we want to conditionally create an index. That would bee too confusing. A contrib, to find "suggested" indexes seems fine. Andreas
> Keep in mind that the penalty for no index is a sequential scan, which > _usually_ is a light operation. In fact, many queryes don't even use > indexes if they are going to need to see more than a small portion of > the table. I agree... Managing customers'DBs for years now, I'm convinced that systematic indexes are good only for the intellect of the DBA because it may respect some methods :-) Too many tables with less than thousands records. Automatic indexes are annoying, I have to drop em all every time. It's harder to think in droping unwanted indexes than creating wanted ones. I know DBAs that drop automatic PK index created by PG only because the naming method choosen for index is not like they want.. :-) Table scans are always good idea for litle tables. Even more if the table is fully cached (I dream of a "CREATE TABLE... CACHE"). Cool too when we'll be able to store execution plans :-) Finaly, there would be tables with more index than data :-) if you consider tables with many FK. Where's the gain then? Best regards, -- Jean-Paul ARGUDO IDEALX S.A.S Consultant bases de données 15-17, av. de Ségur http://IDEALX.com/ F-75007 PARIS
Tom Lane wrote: > We should not *force* people to have an index. If the master table very > seldom changes, then an index on the referencing table will be a net > loss (at least as far as the foreign-key ops go). You'll pay for it on > every referencing-table update, and use it only seldom. Not only that but it's non standard ... people porting code over which correctly defines an explicit index when appropriate would end up with two of them. > Possibly there should be an entry in the "performance tips" chapter > recommending that people consider adding an index on the referencing > column if they are concerned about the speed of updates to the > referenced table. But I dislike software that considers itself smarter > than the DBA. This is a much better idea. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org
On Fri, 21 Dec 2001, Zeugswetter Andreas SB SD wrote: > > > > If you have a foreign key on a column, then whenever the primary key is > > > modified, the following checks may occur: > > > > > > * Check to see if the child row exists (no action) > > > * Delete the child row (cascade delete) > > > * Update the child row (cascade update) > > > > > > All of which will benefit from an index... > > > > OK, then perhaps we should be creating an index automatically? Folks? > > The index is only useful where you actually have an on delete or on update > clause. I don't think we want to conditionally create an index. That would > bee too confusing. A contrib, to find "suggested" indexes seems fine. Actually, even without an on delete or on update it would be used (for the check to see if there was a row to prevent the action), however autocreate seems bad. The contrib thing sounds cool, another vote that way.
Don Baccus writes: > Not only that but it's non standard ... people porting code over which > correctly defines an explicit index when appropriate would end up with > two of them. Not that there's anything remotely standard about indexes... -- Peter Eisentraut peter_e@gmx.net
> > > * Check to see if the child row exists (no action) > > > * Delete the child row (cascade delete) > > > * Update the child row (cascade update) > > > > > > All of which will benefit from an index... > > > > OK, then perhaps we should be creating an index automatically? Folks? > > The index is only useful where you actually have an on delete or on update > clause. Hmm...not necessarily true. A default 'no action' foreign key still needs to prevent the parent key from being deleted if the child exists. This requires that postgres do a search of the child table. > I don't think we want to conditionally create an index. That would > bee too confusing. A contrib, to find "suggested" indexes seems fine. That's what I suggested. Chris
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> If you have a foreign key on a column, then whenever the primary key is > >> modified, the following checks may occur: > >> > >> * Check to see if the child row exists (no action) > >> * Delete the child row (cascade delete) > >> * Update the child row (cascade update) > >> > >> All of which will benefit from an index... > > > OK, then perhaps we should be creating an index automatically? Folks? > > We should not *force* people to have an index. If the master table very > seldom changes, then an index on the referencing table will be a net > loss (at least as far as the foreign-key ops go). You'll pay for it on > every referencing-table update, and use it only seldom. > > Possibly there should be an entry in the "performance tips" chapter > recommending that people consider adding an index on the referencing > column if they are concerned about the speed of updates to the > referenced table. But I dislike software that considers itself smarter > than the DBA. OK, I have added the following to the create_lang.sgml manual page. I couldn't find a good place to put this in the performance page. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 Index: doc/src/sgml/ref/create_table.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_table.sgml,v retrieving revision 1.50 diff -c -r1.50 create_table.sgml *** doc/src/sgml/ref/create_table.sgml 2001/12/08 03:24:35 1.50 --- doc/src/sgml/ref/create_table.sgml 2002/01/03 06:23:36 *************** *** 437,442 **** --- 437,449 ---- </varlistentry> </variablelist> </para> + <para> + If primary key column is updated frequently, it may be wise to + add an index to the <literal>REFERENCES</literal> column so that + <literal>NO ACTION</literal> and <literal>CASCADE</literal> + actions associated with the <literal>REFERENCES</literal> + column can be more efficiently performed. + </para> </listitem> </varlistentry> *************** *** 472,477 **** --- 479,486 ---- </listitem> </varlistentry> </variablelist> + + </refsect1>