On 04/25/2017 08:28 AM, Johann Spies wrote:
On 24 April 2017 at 15:17, Tomas Vondra <tomas.vondra@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
