From: Justin Pryzby <pryzby@telsasoft.com>
To: Scott Rankin <srankin@motus.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Cc: Eric Meaney <emeaney@motus.com>
Subject: Re: Slow Bitmap Index Scan
Date: Wed, 28 Nov 2018 13:17:53 -0600
Message-ID: <20181128191753.GM30707@telsasoft.com> (raw)
In-Reply-To: <05111C93-69B4-4D5B-8AD3-8C9C47E0F01E@motus.com>
References: <05111C93-69B4-4D5B-8AD3-8C9C47E0F01E@motus.com>
On Wed, Nov 28, 2018 at 07:08:53PM +0000, Scott Rankin wrote:
> We recently moved our production database systems from a 9.4 running on a self-managed EC2 instance to 9.6.10 on Amazon’s AWS (same RAM, CPU). After the move, we’re finding that certain queries that we run against a GIN full-text index have some occasionally very slow executions and I’m struggling to figure out what to do about it. I would be very grateful for any ideas!
>
> The setup we have is a 32-core, 244 GB RAM primary with a same-sized read replica. The queries are running off the replica, but performance is roughly the same between the master and the replica.
>
> Here’s a query that’s performing badly:
Can you compare or show the explain(analyze,buffers) for a fast query instance
vs slow query instance ? Is it slower due to index access or heap? Due to
cache misses ?
Also, you have big ram - have you tried disabling KSM or THP ?
https://www.postgresql.org/message-id/20170718180152.GE17566%40telsasoft.com
Justin
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: pryzby@telsasoft.com, srankin@motus.com, pgsql-performance@lists.postgresql.org, emeaney@motus.com
Subject: Re: Slow Bitmap Index Scan
In-Reply-To: <20181128191753.GM30707@telsasoft.com>
* 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