Re: [PERFORM] Delete, foreign key, index usage

> On Wed, Apr 5, 2017 at 6:40 AM, Johann Spies <johann.spies@gmail.com> wrote:
>>
>> On 4 April 2017 at 14:07, Johann Spies <johann.spies@gmail.com> wrote:
>>
>> > Why would that be?
>>
>> To answer my own question.  After experimenting a lot we found that
>> 9.6 uses a parallel seqscan that is actually a lot faster than using
>> the index on these large tables.

Further experimenting resulted in a solution which we do not understand:

The table 'publication'  had the field 'ut' as primary key and the ut
index was not used.

So we built an additional btree index(ut) on publication - which was
ignored as well.
Then we built a gin index(ut) on publication and now it is being used.

The same happened on the other table (belongs_to) where the btree
index was ignored by the planner but the gin-index used.

As a result our deletes runs between 25-60 times faster than earlier
with maximum of about 200000 records per hour in comparison with a
maximum of 4500 earlier..

In the case of both tables the ut has a foreign key reference to
another article.

Why would the planner prefer the use the gin index and not the btree
index in this case?

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