All geek questions in one place

Postgres EXPLAIN ANALYZE is much faster than normal query execution

I am trying to optimize a PostgreSQL 8.4 query. After you greatly simplified the original query, trying to find out why it chooses a bad query plan, I got to the point that executing a query under EXPLAIN ANALYZE takes only 0.5 s, and it usually takes 2.8 seconds to start it. It seems obvious that what EXPLAIN ANALYZE shows is not what he usually does, so anything that shows me is useless, isn't it? What is going on here and how do I see what he really does?

+3
source share
2 answers

Most likely, the data pages are in the OS cache when you manually run EXPLAIN ANALYZE to try to optimize the query. When launched in a normal environment, the pages are probably no longer in the cache and should be ejected from disk, which increases the execution time.

+2
source

This shows less time because:

1) The total execution time shown EXPLAIN ANALYZEincludes start-up time and stop-start time, as well as the start time of any triggers that are started, but does not include parsing, rewriting, or scheduling time.

2) Since the output is not transmitted to the client, transmission costs and input-output costs are not included.

Attention!

, EXPLAIN ANALYZE, , gettimeofday(). EXPLAIN (ANALYZE TRUE, TIMING FALSE).

+1

Source: https://habr.com/ru/post/1758311/

More articles:


All Articles