Date: Wed, 28 Nov 2018 13:17:53 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Scott Rankin <srankin@motus.com>
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>,
	Eric Meaney <emeaney@motus.com>
Subject: Re: Slow Bitmap Index Scan
Message-ID: <20181128191753.GM30707@telsasoft.com>
References: <05111C93-69B4-4D5B-8AD3-8C9C47E0F01E@motus.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <05111C93-69B4-4D5B-8AD3-8C9C47E0F01E@motus.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

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