Re: Slow Bitmap Index Scan

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’sAWS (same RAM, CPU).  After the move, we’re finding that certain queries that we run against a GIN full-text
indexhave some occasionally very slow executions and I’m struggling to figure out what to do about it.   I would be
verygrateful 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