Re: costly foreign key ri checks (4)

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> * I changed the message wording to conform to the message style
>> guidelines. I also made it complain about "costly sequential scans"
>> instead of "costly cross-type conversion", since ISTM that's what's
>> really at issue here. I'm not completely wedded to that wording
>> though, if anyone feels the previous version was better.

> So the issue wasn't that the conversion was costly, but that an index
> couldn't be used to look up the primary key?

Exactly. That's why the patch is looking for operator membership in the
index's opclass.

There are actually two cases where the RI check can use the index even
though the datatypes aren't identical:

1. There's no cross-type equality operator, but there is an implicit
coercion from the referencing column's type to the referenced column's
type. In this case the selected equality operator will actually take
the referenced column's type on both sides, and will naturally be a
member of the index opclass.

2. There is a cross-type operator and it's a member of the referenced
column's index opclass.

Case 2 is new in 7.5 but case 1 has always existed. Note that case 1
does include a type conversion, but it can still use the index and will
be plenty fast enough.

The cases where we lose and have to use a seqscan, even though a
relevant equality operator was found, are:

3. Implicit coercion occurred on the primary key side instead of
the referencing column side. In this case the RI query becomes
"WHERE pk::something = fk", and the PK index doesn't apply.

4. A cross-type operator was selected and it's not in the PK opclass.

In case 4, we are slow even though there isn't necessarily any
cross-type conversion happening at all (depending on what goes on inside
the cross-type operator).

So it seems to me that blaming the problem on conversion per se is a
bit wide of the mark.

regards, tom lane