On pg 9.6.* we have sometimes strange problem with some sessions. We experienced it from bash scripts, golang program and node.js applications. Environment are Google Compute Engine instances with Debian 8.
Sometimes some session goes into some kind of "drowsy" state and runs incredibly slowly. It causes some very low disk IO and runs like 20x slower then usual. This happens mainly when database is under heavy load and it happens randomly to different tasks. It happens randomly to both connections from local instance (cronjob running on instance with PG) and remote connections from other instances.
One possibility is that another process inserted a bunch of new tuples and committed them, and now the existing session has to dig through all those new tuples and ignore them because they are too new to be visible to it. This would particularly be a problem where the queries have things like "MAX(indexed_column)" or "ORDER BY indexed_column desc LIMIT 1" and where the newly insert tuples all have values which are at the end of the range being probed by that query. Although I don't know why this would have gotten worse from 9.5 to 9.6. The change should have been in the other direction.
