Thread: [PERFORM] PSA: upgrade your extensions
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 :-). merlin
Hi Merlin: Any EXPLAIN on the query affected? size of indexes before and after reindex? Regards, Daniel. > El 1 feb 2017, a las 13:38, Merlin Moncure <mmoncure@gmail.com> escribió: > > 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 :-). > > merlin > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance
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).
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.
Cheers,
Jeff
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