Re: Delete, foreign key, index usage

On 4 April 2017 at 14:07, Johann Spies <johann(dot)spies(at)gmail(dot)com> wrote:

> While updating our database which includes a lot of deletions where a lot
> of foreign key references are involved we found that in the case of two
> tables the indexes are ignored and it slow down the process a lot.
> ...
>

>
> Why would that be?
>

After a long time we found the problem: The primary/foreign key fields had
different types: varchar and citext. In the case of the two tables where
the indexes were ignored indexes were built with the 'citext' type and the
queries assumed it was varchar as the case were in the other tables using
the same field.

Lesson learnt: Check your types in every field in every table - and we
have many tables.

Regards
Johann

--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)