Re: Delete, foreign key, index usage

[Johann Spies <johann.spies@xxxxxxxxx>]

On 4 April 2017 at 14:07, Johann Spies <johann.spies@xxxxxxxxx> 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)