From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Johann Spies <johann.spies@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Delete, foreign key, index usage
Date: Wed, 26 Apr 2017 01:35:38 +0200
Message-ID: <8ad602a3-d623-d8d2-2f1a-c1b55b3258bf@2ndquadrant.com> (raw)
In-Reply-To: <CAGZ55DTi5XqWdbbfWc_rRfzckUgZZoJBAd5Ku=0hagk-0xuNaQ@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, johann.spies@gmail.com
Subject: Re: Delete, foreign key, index usage
In-Reply-To: <8ad602a3-d623-d8d2-2f1a-c1b55b3258bf@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox