Обсуждение: 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)
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. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 24 April 2017 at 15:17, Tomas Vondra <tomas.vondra@2ndquadrant.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)
On 25 April 2017 at 18:28, Johann Spies <johann.spies@gmail.com> wrote: > On 24 April 2017 at 15:17, Tomas Vondra <tomas.vondra@2ndquadrant.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
On 04/25/2017 08:28 AM, Johann Spies wrote: > On 24 April 2017 at 15:17, Tomas Vondra <tomas.vondra@2ndquadrant.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