Explain to me postgresql EXPLAIN please
Yesterday I was looking at the query that took around 25 seconds to execute on our prod read replica. I ran explain anaylyze to see what can be the issue and what can be done immediately as this is something that constantly timeouts on a production. Here’s the part of the explain graph that I was looking at the most:
From this graph I could imagine that the biggest problem here is the gather merge, then hash join. But no immediate actions could be done to those, at least as far as I can understand. I figure, I’d at least fix the sorting, even though it’s not super important.
An important special case is ORDER BY in combination with LIMIT n: an explicit sort will have to process all the data to identify the first n rows, but if there is an index matching the ORDER BY, the first n rows can be retrieved directly, without scanning the remainder at all
So that’s what I did and here’s before and after:
From 23.52 seconds of execution to 1.45 ms. Pretty impressive I’d say, but how was I supposed to know that this is the real bottleneck?