Re: Delete, foreign key, index usage

On 24 April 2017 at 15:17, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
> On 04/24/2017 08:48 AM, Johann Spies wrote:
>>
>>
>> Why would the planner prefer the use the gin index and not the btree
>> index in this case?
>>
>
> You'll need to show what queries are you running - that's a quite important
> piece of information, and I don't see it anywhere in this thread. Seeing
> explain plans would also be helpful.

It is a simple "delete from wos_2017_1.article;" which causes a domino
effect deletes due to foreign keys. In the case of one table with more
than 50 million records where the primary key was also the foreign
key, the process only started to use the index when we built a gin
index. In the case of the "belongs_to" table (shown in my first
email) we first built a btree index on the foreign key - and it was
ignored. Only after the gin index was created did it use the index.

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