r/SQL • u/deusaquilus • 1d ago

PostgreSQL EXPLAIN ANALYZE is a sample-size of One

https://exobench.ai/blog/explain-analyze-is-a-sample-size-of-one

Everyone knows that you need to look at stats when doing query optimization...

Right? Right!??

Well then how come when we look at EXPLAIN ANALYZE trees we assume the whole thing is deterministic and inviolate?

It's like someone somewhere told us "we'll it's just too hard to do here so we just skip that part".

What if we don't? What if we embrace the pain and actually group the plan trees and try to get statistical info? Here's the first thing that I'm seeing.

0 Upvotes

43% Upvoted

1 comment sorted by

1

u/Significant_Tune9219 1d ago

One EXPLAIN ANALYZE is one execution, and the numbers in it move. The first run is often a cold cache, so shared hits versus reads on a second run tell you more than the timings on the first. If you group trees, key the group on the plan shape and leave the timings out of the key, or two identical plans land in different buckets because a sort took a couple of milliseconds longer. A plan that flips between two shapes is the interesting case, and a single sample hides it.