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 me with this:

Planning time: 0.286 ms
Execution time: 127.233 ms

Why is this ? The difference is too big I think

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-4.
• 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.

---

Notice: Execution time not includes Planning time, you can try explain analyse select 1 to see a case where PlanningTime>ExecutionTime.