Re: [PERFORM] PSA: upgrade your extensions

On Thu, Feb 2, 2017 at 1:18 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Wed, Feb 1, 2017 at 4:38 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>
>> I was just troubleshooting a strange performance issue with pg_trgm
>> (greatest extension over) that ran great in testing but poor in
>> production following a 9.6 in place upgrade from 9.2. By poor I mean
>> 7x slower.  Problem was resolved by ALTER EXTENSION UPDATE followed by
>> a REINDEX on the impacted table.  Hope this helps somebody at some
>> point :-).
>
> It was probably the implementation of the triconsistent function for pg_trgm
> (or I would like to think so, anyway).

Yeah, this is definitely the case. We are seeing 50-80% runtime
reduction in many common cases, with the problematic cases being in
the upper end of that range.

> But if so, the REINDEX should not have been necessary, just the ALTER
> EXTENSION UPDATE should do the trick. Rebuiding a large gin index can be
> pretty slow.

Hm, I thought it *was* necessary, in my poking.  However the evidence
is destroyed and it's not worth restaging the test, so I'll take your
word for it.

merlin