Why Planing time and Execution time are so different Postgres?

Question

I make such a query

EXPLAIN (ANALYZE, BUFFERS)
        SELECT COUNT(id) q, day
        FROM my_table
        WHERE role_id && ARRAY[15, 17]
        GROUP BY "day"
        ORDER BY "day" DESC;

And Postgres responds to me with this:

Planning time: 0.286 ms
Execution time: 127.233 ms

Why is this? The difference is too big I think

Lezlielg

Why do you think that? Why do you think there should be a relation between planning time and execution time at all? – Leeuwarden

I am sorry, I considered it in a wrong way. – Lezlielg

So short answer is: Planning time is not the expected execution time, but time spent guessing how to execute query. I was understanding it in the wrong way too. – Picoline

Answer 1

I think there's small misunderstanding of yours. I try to describe shortly what's going on when you run a query:

1. You write a query in SQL which is some kind of "script" that you try to tell the server what you want from him.
2. Most of the times there is many ways for server to collect data you ask for by writing query. There is where mechanism called "query planner" comes in to action. It tries to find the quickest way (plan) of execution of your query. It's doing so by estimates execution time of several possible ways (plans).
3. Server runs the query using the plan which is thought as the quickest one.
4. Server returns you the output.

EXPLAIN command prints you description of that process. Now:

• Execution time on EXPLAIN output is time server spent on steps 3 only.
• Planning time on EXPLAIN output is time server spent on step 2 only. I believe you think of it as "time planner thinks that query would take", but that can be called "planned [execution] time" or "estimated execution time".

So there's no reason why planning time and execution time difference should be smaller. PostgreSQL want to keep planning time short to minimize it's impact on whole execution time.

All is written here in manual.

---

Note that the execution time does not include the planning time. If you want a clear example where the planning time alone is longer than the execution time, you can try query explain analyse select 1.

Subvention

And what the Total query runtime means on the history when using the explain command – Eton

@Gabriel's Messanger - excellent answer - polite, thorough, easy to understand and with a link to documentation. :) – Bokbokhara

Note, execution time does not include step 2. I have seen explain times higher than execution times so they are not overlapping. – Xever

Yes, execution time is only the step 3. And when you ask the server to run explain analyze the output is text which is generated before transmitting the output from server to client, so obviously step 4 cannot be included either. – Primavera

Answer 2

The EXPLAIN ANALYSE command does two things: It computes the time Postgres required to figure out a query plan and second, it determines how long the query took to execute. It is expected that coming up with a query plan is much quicker than executing the query itself. It might be possible to give you a better explanation if you provided the full output of the EXECUTE command. The documentation contains a rather detailed explanation.

Charlacharlady

It's ANALYZE not ANALYSE – Stevenson

@Gabriel'sMessanger: both is valid – Leeuwarden

PostgreSQL comforts both the English language and American English language. Both ANALYZE and ANALYSE are valid. – Kirman