Re: GIN index not used if created in the same transaction as query

Adam Brusselback <adambrusselback(at)gmail(dot)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