Every now and again, I will encounter an unexplained long-running query.
It’s a head scratcher moment, because this query that is still running for 20 minutes (not blocking) can be run independently in about 500ms
I can only assume that the problem query ran against the table(s) at a time when it was perhaps in need of a vacuum analyze... I’m guessing here, that the table had seen some amount of change and simply had out of date statistics.
How can I avoid this?
The auto-vacuum daemon is doing it’s thing, but there is always going to be an opportunity for a query to sneak in against a table that has recently seen large change, but not yet been analysed.
On the application side, we can explicitly issue a VACUUM ANALYZE after each bulk operation - and often that is precisely what happens..
But - I am keenly aware that this cannot be performed within a transaction. That means there is always a (small) window in which a query can still execute in this scenario.
Are there any other best practices that can mitigate this kind of problem?
It’s rare, sure - but I don’t like sweeping these under the rug.
I’m on PG 9.6.. perhaps there are planner improvements since then that might reduce the incidence of these (rare) issues.
Any advice appreciated, thanks.
