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

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

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

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

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

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

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