Adam Brusselback <adambrusselback@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
