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


Fri, 21 Dec 2001 10:47:01 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
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

Thu, 20 Dec 2001 22:58:40 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: contrib idea
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
>


Fri, 21 Dec 2001 12:03:25 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: contrib idea
> 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, Pennsylvania 19026

Thu, 20 Dec 2001 23:03:53 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: contrib idea
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

Thu, 20 Dec 2001 23:29:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: contrib idea 
> 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


Fri, 21 Dec 2001 12:33:09 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: contrib idea 
> 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, Pennsylvania 19026

Thu, 20 Dec 2001 23:35:39 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: contrib idea
> 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

Fri, 21 Dec 2001 10:12:59 +0100
Jean-Paul ARGUDO <jean-paul.argudo@IDEALX.com>
Re: contrib idea
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


Fri, 21 Dec 2001 08:09:59 -0800
Don Baccus <dhogaza@pacifier.com>
Re: contrib idea
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


Sat, 22 Dec 2001 09:30:47 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: contrib idea
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>
Thu, 3 Jan 2002 01:25:52 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: contrib idea