Re: Delete, foreign key, index usage

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> On Wed, Apr 5, 2017 at 6:40 AM, Johann Spies <johann.spies@xxxxxxxxx> wrote:
>>
>> On 4 April 2017 at 14:07, Johann Spies <johann.spies@xxxxxxxxx> 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)


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux