Re: Delete, foreign key, index usage

On 25 April 2017 at 18:28, Johann Spies <johann(dot)spies(at)gmail(dot)com> 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.

Some suggestions:

(It's a good idea to CC the person you're replying to so that they're
more likely to notice the email)

psql's \d output for the referenced and referencing table would be a
good thing to show too.

This would confirm to us things like;

* you've got the indexes defined correctly
* there's nothing weird like the indexes are on some other tablesspace
with some other random_page_cost defined on it which is causing them
not to ever be preferred.
* you've actually got indexes

Also, you might like to try to EXPLAIN DELETE FROM wos_2017_1.article
WHERE ut = '<some constant>'; to see if the planner makes use of the
index for that. If that's not choosing the index then it might be an
easier issue to debug.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services