Today I experimented using explain analyze to extract the execution times of my queries. I was hoping to properly compute their execution times without the impact of query latencies, however in the documentation it states that there is a profiling overhead instead:
In order to measure the run-time cost of each node in the execution plan, the current implementation of EXPLAIN ANALYZE adds profiling overhead to query execution. As a result, running EXPLAIN ANALYZE on a query can sometimes take significantly longer than executing the query normally. The amount of overhead depends on the nature of the query, as well as the platform being used. The worst case occurs for plan nodes that in themselves require very little time per execution, and on machines that have relatively slow operating system calls for obtaining the time of day.
As a result, I am questioning the usefulness of explain analyze as it seems you simply trade a latency with profiling overhead. More surprisingly, timing my database queries in code (Python in my case with the psycopg2 library) results in lower query execution times.
EXPLAIN (ANALYZE) is a optimization tool and by no way an optimizer. That means that it adds a real overhead and should never be used in a application in production, but only at tuning time.
It is a precious tool for a database developper or administrator to understand exactly how the database engine will execute a specific query, and where most of the time is spent. It can help for example to determine the impact of adding or removing an index with the detailed impact on the queries both in times and accesses.
Because as soon as you use a database, you cannot rely on simple time measurement, because they can depend on whether to database is loaded with other queries, whether some pages are already cached in memory, not speaking of the external load on the client or server machine.