Thread: contrib idea

contrib idea

From
"Christopher Kings-Lynne"
Date:
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



Re: contrib idea

From
Bruce Momjian
Date:
> 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
 


Re: contrib idea

From
Bruce Momjian
Date:
> 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
 


Re: contrib idea

From
"Christopher Kings-Lynne"
Date:
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
>



Re: contrib idea

From
Tom Lane
Date:
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


Re: contrib idea

From
Bruce Momjian
Date:
> 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
 


Re: contrib idea

From
"Christopher Kings-Lynne"
Date:
> 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



Re: contrib idea

From
"Zeugswetter Andreas SB SD"
Date:
> > 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


Re: contrib idea

From
Jean-Paul ARGUDO
Date:
> 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


Re: contrib idea

From
Don Baccus
Date:
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



Re: contrib idea

From
Stephan Szabo
Date:
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.




Re: contrib idea

From
Peter Eisentraut
Date:
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



Re: contrib idea

From
"Christopher Kings-Lynne"
Date:
> > > * 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



Re: contrib idea

From
Bruce Momjian
Date:
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>