Thread: Question about Foreign key constraint causes "costly sequential scans"?

Greetings,

I have a question about foreign key cost. I have two tables A and B.
A has 300,000 records, B has 3 records.

A (col1, col2, col3... ... colN)

B (colB1 primary key, colB2)

I'd like to setup foreign key constraint for A.col3, as the following:
CONSTRAINT Aclo3_fk FOREIGN KEY (col3) REFERENCES B(colB1)

But I got a warning msg from postgresql as:

   foreign key constraint "Aclo3_fk" will require costly sequential scans

Some comments about it?

Thanks a lot!
Emi

Emi Lu <emilu@cs.concordia.ca> writes:
> But I got a warning msg from postgresql as:
>    foreign key constraint "Aclo3_fk" will require costly sequential scans

Try to make the referencing and referenced columns the same datatype.

            regards, tom lane

On Tue, 2005-08-23 at 10:30 -0400, Emi Lu wrote:
> I'd like to setup foreign key constraint for A.col3, as the following:
> CONSTRAINT Aclo3_fk FOREIGN KEY (col3) REFERENCES B(colB1)
>
> But I got a warning msg from postgresql as:
>
>    foreign key constraint "Aclo3_fk" will require costly sequential
> scans

Index the foreign key:

    create index a_col3_ix on a (col3);

and then drop an recreate the foreign key and see if the warning is
still there.

As a rule I index foreign keys.

On Tue, Aug 23, 2005 at 10:30:14AM -0400, Emi Lu wrote:
> I'd like to setup foreign key constraint for A.col3, as the following:
> CONSTRAINT Aclo3_fk FOREIGN KEY (col3) REFERENCES B(colB1)
>
> But I got a warning msg from postgresql as:
>
>   foreign key constraint "Aclo3_fk" will require costly sequential scans

Is there not a DETAIL message following this warning?  It should
explain the problem: the referring and the referred-to columns are
of different types.

Additionally, it's usually a good idea to create an index on the
referring column (A.col3) to speed up referential integrity checks
when modifying the referred-to table (B).

--
Michael Fuhr

Thanks a lot for all helps. I do not have warnings anymore  :-)


>>I'd like to setup foreign key constraint for A.col3, as the following:
>>CONSTRAINT Aclo3_fk FOREIGN KEY (col3) REFERENCES B(colB1)
>>
>>But I got a warning msg from postgresql as:
>>
>>  foreign key constraint "Aclo3_fk" will require costly sequential scans
>>
>>
>
>Is there not a DETAIL message following this warning?  It should
>explain the problem: the referring and the referred-to columns are
>of different types.
>
>Additionally, it's usually a good idea to create an index on the
>referring column (A.col3) to speed up referential integrity checks
>when modifying the referred-to table (B).
>
>
>

On Tue, Aug 23, 2005 at 02:53:33PM +0000, Matt Miller wrote:
> On Tue, 2005-08-23 at 10:30 -0400, Emi Lu wrote:
> > I'd like to setup foreign key constraint for A.col3, as the following:
> > CONSTRAINT Aclo3_fk FOREIGN KEY (col3) REFERENCES B(colB1)
> >
> > But I got a warning msg from postgresql as:
> >
> >    foreign key constraint "Aclo3_fk" will require costly sequential
> > scans
>
> Index the foreign key:
>
>     create index a_col3_ix on a (col3);
>
> and then drop an recreate the foreign key and see if the warning is
> still there.

That's a good idea, but it's not the condition that elicits the "will
require costly sequential scans" warning.  That warning is caused by
the keys being of different types (see ATAddForeignKeyConstraint()
in src/backend/commands/tablecmds.c in the source code for 8.0 and
later).

--
Michael Fuhr