From: Tom Lane <tgl@sss.pgh.pa.us>
To: Adam Brusselback <adambrusselback@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: GIN index not used if created in the same transaction as query
Date: Fri, 19 May 2017 11:33:21 -0400
Message-ID: <31274.1495208001@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMjNa7c_=5pZL8T-kBhiRb0=an=DbpGg5jsYZ9guDs8i75jwcg@mail.gmail.com>
References: <CAMjNa7c_=5pZL8T-kBhiRb0=an=DbpGg5jsYZ9guDs8i75jwcg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
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: tgl@sss.pgh.pa.us, adambrusselback@gmail.com
Subject: Re: GIN index not used if created in the same transaction as query
In-Reply-To: <31274.1495208001@sss.pgh.pa.us>
* 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