Subject: Re: Delete, foreign key, index usage
To: Johann Spies <johann.spies@gmail.com>, pgsql-performance@postgresql.org
References: 
 <CAGZ55DRKE8Jkj+aE+vxeQqC+uP46ujH5fAgL1vZLqafdeKZRiQ@mail.gmail.com>
 <CAGZ55DRr=wVHJgfrBb9eGVWyL81ObJw=7kpfxpfYyeS4osTKmw@mail.gmail.com>
 <CAMAYy4KMt-NxqmhNmLr5gxH_WOJqnmP17BGXCj1G37YMk2jR2Q@mail.gmail.com>
 <CAGZ55DS9Y3KPZV-XBtU5H5COLGBO1BGMZhwFfVeM5dLhuTyB2A@mail.gmail.com>
 <54e42679-7ec5-b097-db7b-f04c9abfc3c6@2ndquadrant.com>
 <CAGZ55DTi5XqWdbbfWc_rRfzckUgZZoJBAd5Ku=0hagk-0xuNaQ@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <8ad602a3-d623-d8d2-2f1a-c1b55b3258bf@2ndquadrant.com>
Date: Wed, 26 Apr 2017 01:35:38 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.4.0
MIME-Version: 1.0
In-Reply-To: 
 <CAGZ55DTi5XqWdbbfWc_rRfzckUgZZoJBAd5Ku=0hagk-0xuNaQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


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