Finding query execution time using \timing and EXPLAIN ANALYZE..

[Satalabaha Postgres <satalabaha.postgres@xxxxxxxxx>]

Hi Listers,

I'm new to PostgreSQL. So please bear with me for any funny questions.

I was trying to get elapsed time for the sql using the \timing command in psql as well as EXPLAIN ANALYZE.. I see both the commands reporting different elapsed time. And this is after repeated execution of the same query and not the first execution. In this case, which one would be the right execution time for the query.

\timing in psql

=======================

session1=>select count(1) from demo;
  count
---------
 1000001
(1 row)

Time: 274.661 ms <<<<<

Using EXPLAIN ANALYZE..

============================

session1=>EXPLAIN(ANALYZE,BUFFERS,SETTINGS,TIMING) select count(1) from demo;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=25439.55..25439.56 rows=1 width=8) (actual time=66.963..68.989 rows=1 loops=1)
   Buffers: shared hit=19231
   ->  Gather  (cost=25439.34..25439.55 rows=2 width=8) (actual time=64.863..68.980 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=19231
         ->  Partial Aggregate  (cost=24439.34..24439.35 rows=1 width=8) (actual time=62.059..62.060 rows=1 loops=3)
               Buffers: shared hit=19231
               ->  Parallel Seq Scan on demo  (cost=0.00..23397.67 rows=416667 width=0) (actual time=0.007..35.838 rows=333334 loops=3)
                     Buffers: shared hit=19231
 Settings: effective_cache_size = '43882472kB', maintenance_io_concurrency = '1'
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.072 ms
 Execution Time: 69.023 ms <<<<

Thanks,

Satalabaha