25

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

funnydman
  • 9,083
  • 4
  • 40
  • 55
Thor Samsberg
  • 2,219
  • 5
  • 22
  • 30
  • Why do you think that? Why do you think there should be a relation between planning time and execution time at all? –  Nov 05 '15 at 17:35
  • I am sorry, I considered it in a wrong way. – Thor Samsberg Nov 05 '15 at 20:57
  • 2
    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. – EAmez Jul 16 '19 at 08:56

2 Answers2

64

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.

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.

Mikko Rantalainen
  • 14,132
  • 10
  • 74
  • 112
Gabriel's Messanger
  • 3,213
  • 17
  • 31
  • And what the `Total query runtime` means on the history when using the `explain command` – Slim Nov 08 '17 at 12:38
  • 1
    @Gabriel's Messanger - excellent answer - polite, thorough, easy to understand and with a link to documentation. :) – fanny Nov 09 '17 at 16:39
  • 1
    Note, execution time does *not* include step 2. I have seen explain times higher than execution times so they are not overlapping. – Chris Travers Jan 25 '18 at 09:20
1

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.

antiguru
  • 1,101
  • 11
  • 18