Re: Delete, foreign key, index usage

On 04/25/2017 08:28 AM, Johann Spies wrote:
> 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

Wouldn't it be easier to simply show the queries (with the exact
condition) and the associated explain plans? I understand you're doing
your best to explain what's happening, but the explain plans contain a
lot of information that you might have missed.

I suppose you actually did explain analyze to verify the query was not
using the btree index and then started using the gin index. Or how did
you verify that?

Also, which PostgreSQL version have you observed this on? I see you've
mentioned 9.6 when talking about parallel scans, but I suppose the issue
was originally observed on some older version.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services