Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres EXPLAIN ANALYZE is much faster than running the query normally

I'm trying to optimise a PostgreSQL 8.4 query. After greatly simplifying the original query, trying to figure out what's making it choose a bad query plan, I got to the point where running the query under EXPLAIN ANALYZE takes only 0.5s, while running it normally takes 2.8s. It seems obvious then, that what EXPLAIN ANALYZE is showing me is not what it normally does, so whatever it's showing me is useless, isn't it? What is going on here and how do I see what it's really doing?

like image 599
EMP Avatar asked Aug 06 '10 02:08

EMP


1 Answers

Most likely, the data pages are in the OS disk cache when you are manually running with EXPLAIN ANALYZE in order to try and optimize the query. When run in a normal environment, the pages probably aren't in the cache already and have to be fetched from disk, increasing the runtime.

like image 184
Matthew Wood Avatar answered Sep 28 '22 08:09

Matthew Wood