From: | Johann Spies <johann(dot)spies(at)gmail(dot)com> |
---|---|
To: | "pgsql-performa(dot)" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Delete, foreign key, index usage |
Date: | 2017-04-24 06:48:56 |
Message-ID: | CAGZ55DS9Y3KPZV-XBtU5H5COLGBO1BGMZhwFfVeM5dLhuTyB2A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> On Wed, Apr 5, 2017 at 6:40 AM, Johann Spies <johann(dot)spies(at)gmail(dot)com> wrote:
>>
>> On 4 April 2017 at 14:07, Johann Spies <johann(dot)spies(at)gmail(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2017-04-24 13:17:18 | Re: Delete, foreign key, index usage |
Previous Message | Pavel Stehule | 2017-04-21 07:12:53 | Re: Query with no result set, really really slow adding ORBDER BY / LIMIT clause |