Search code examples
postgresqlquery-planner

Why Planing time and Execution time are so different Postgres?

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

Solution

  • I think you have a small misunderstanding. I'll try to describe what's going on when you run a query:

    1. You write a query in SQL which is a "script" telling the server what you want from it.
    2. Most of the time there are many ways for the server to collect the data you ask for when you write your query. This is where the mechanism called the "query planner" comes into action. It tries to find the fastest plan of execution for your query. It does so by estimating the execution time for several possible plans.
    3. The server runs the query using the plan that it determined was the fastest.
    4. The server returns the output of running your query.

    The EXPLAIN command prints a description of that process. Now:

    • The Execution time on the EXPLAIN output is the time server spent on step 3 only.
    • The Planning time on the EXPLAIN output is the time the server spent on step 2 only. I believe you can think of it as "the time the planner thinks the query will take", which we can call "planned [execution] time" or "estimated execution time".

    So there's no reason to expect the planning time and execution time to be similar. PostgreSQL wants to keep the planning time short to minimize its impact on the whole execution time.

    You can find more information here in the manual.

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