On 25 April 2017 at 18:28, Johann Spies <johann.spies@xxxxxxxxx> wrote: > On 24 April 2017 at 15:17, Tomas Vondra <tomas.vondra@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
