19

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

Gabriel's Messanger
  • 2,913
  • 16
  • 31
Albert Tobac
  • 2,011
  • 5
  • 18
  • 30

2 Answers2

53

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.

Peter Krauss
  • 12,407
  • 21
  • 149
  • 275
Gabriel's Messanger
  • 2,913
  • 16
  • 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,081
  • 12
  • 18