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?
A:
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.
Matthew Wood
2010-08-06 14:52:21
I don't understand - if they were in the cache when I ran EXPLAIN ANALYZE then why aren't they in there when I run without EXPLAIN immediately after?
Evgeny
2010-08-08 23:41:22
Sorry, I misunderstood the order. Now, I would say that it's more likely that the difference is network throughput. I recommend adding a LIMIT clause and trying varying amounts of records (like 1,5,10,100,1000,10000, etc) until you reach your max and compare the times. I'm guessing it will scale roughly as "a+(t*n)" where a is your EXPLAIN ANALYZE time, t is a rough constant of rows per second transferred and n is your number of rows. Obviously, this won't be exact, but I'm guessing it would trend towards it.
Matthew Wood
2010-08-09 14:10:37