Re: [PERFORM] GIN index not used if created in the same transaction as query - Mailing list pgsql-performance

From Tom Lane
Subject Re: [PERFORM] GIN index not used if created in the same transaction as query
Date
Msg-id 31274.1495208001@sss.pgh.pa.us
Whole thread Raw
In response to [PERFORM] GIN index not used if created in the same transaction as query  (Adam Brusselback <adambrusselback@gmail.com>)
Responses Re: [PERFORM] GIN index not used if created in the same transactionas query  (Adam Brusselback <adambrusselback@gmail.com>)
List pgsql-performance
Adam Brusselback <adambrusselback@gmail.com> writes:
> I have a function which builds two temp tables, fills each with data (in
> multiple steps), creates a gin index on one of the tables, analyzes each
> table, then runs a query joining the two.
> My issue is, I am getting inconsistent results for if the query will use
> the index or not (with the exact same data each time, and no differences in
> the stats stored on the table between using the index or not).

Does the "multiple steps" part involve UPDATEs on pre-existing rows?
Do the updates change the column(s) used in the gin index?

What this sounds like is that you're getting "broken HOT chains" in which
there's not a unique indexable value among the updated versions of a given
row, so there's an interval in which the new index isn't usable for
queries.  If that's the correct diagnosis, what you need to do is create
the gin index before you start populating the table.  Fortunately, that
shouldn't create a really horrid performance penalty, because gin index
build isn't optimized all that much anyway compared to just inserting
the data serially.

            regards, tom lane


pgsql-performance by date:

Previous
From: Adam Brusselback
Date:
Subject: [PERFORM] GIN index not used if created in the same transaction as query
Next
From: Adam Brusselback
Date:
Subject: Re: [PERFORM] GIN index not used if created in the same transactionas query