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
I think there's small misunderstanding of yours. I try to describe shortly what's going on when you run a query:
EXPLAIN
command prints you description of that process. Now:
EXPLAIN
output is time server spent on steps 3 only.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
.