Steve Tucknott wrote:

Yes

Cheers
Noel

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

PostGreSql 7.4.3

Am I right in assuming that a foreign key on a table does not explicity
create an index on that column on the foreign table? I can see via the
\d on the table that the foreign key exists, but accessing the table on
that column seems to use a sequential scan.


Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

On Fri, Aug 27, 2004 at 09:31:51AM +0100, Steve Tucknott wrote:
Adding the foreign key won't add the index itself, but I believe that
foreign keys can only be declared on columns declared unique.
Since declaring columns unique implicitly creates an index on them, if
you can legally declare the foreign key then "\d" should show that
the foreign key's index already exists.

It sounds like sequential scan is being used for a completely
unrelated reason.

- Jeremy

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Jeremy Semeiks <jrs (AT) farviolet (DOT) com> writes:
Right, the referenced column must have an index. However, the system
does not require the referencING column to have an index. This can be a
performance loss --- in particular when deleting records from the
referencED table, because the FK machinery then has to seqscan to see
if there are any matching referencING rows. If your master table is
pretty stable, though, you may not care enough to pay the costs of
keeping an index on the slave table.

You can also get burnt if the referenced and referencing columns aren't
of the exact same datatype --- again, not enforced by the system, but a
good way to shoot yourself in the foot performance-wise.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Fri, Aug 27, 2004 at 09:31:51 +0100,
Steve Tucknott <steve (AT) retsol (DOT) co.uk> wrote:
Do the data types match?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Thanks all,
The data types are correct (we use serial/integer fields for the prime
and foreign key references).
Where I had gone wrong was in assuming that the slave foreign key
declaration created an index on that column on the slave table (it seems
to in Informix - where I'm converting from) - I have modified my table
create scripts to create the indexes explicitly. Conversion is looking
good so far!

On Fri, 2004-08-27 at 20:13, Tom Lane wrote:

Jeremy Semeiks <jrs (AT) farviolet (DOT) com> writes:
Right, the referenced column must have an index. However, the system
does not require the referencING column to have an index. This can be a
performance loss --- in particular when deleting records from the
referencED table, because the FK machinery then has to seqscan to see
if there are any matching referencING rows. If your master table is
pretty stable, though, you may not care enough to pay the costs of
keeping an index on the slave table.

You can also get burnt if the referenced and referencing columns aren't
of the exact same datatype --- again, not enforced by the system, but a
good way to shoot yourself in the foot performance-wise.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match



Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769