Re: Delete performance

Arnau <arnaulist(at)andromeiberica(dot)com> writes:
>> The usual cause of slow deletes is that (a) the table is the target of
>> some foreign key references from other large tables, and (b) the
>> referencing columns in those tables aren't indexed.

> This is a thing I don't understand, as far as I know the foreign keys
> references to primary keys and postgresql creates itself and index over
> the primary key, so those columns always should be indexed. Taking into
> account Tom's observation I'm missing something, could you explain it to
> all of us :)

The referencED column is forced to have an index. The referencING
column is not. The cases where you need an index on the latter are
precisely updates/deletes of the referencED column.

In the old version you are using you can also get burnt by datatype
mismatches --- the foreign key mechanism will allow that as long as
it can find an equality operator for the two types, but that equality
operator might not be indexable.

regards, tom lane