Why is Postgres EXPLAIN ANALYZE execution_time different than when I run the actual query?

I'm using a database client to test.

Using EXPLAIN ANALYZE:

Hash Join  (cost=5.02..287015.54 rows=3400485 width=33) (actual time=0.023..1725.842 rows=3327845 loops=1)
  Hash Cond: ((fact_orders.financial_status)::text = (include_list.financial_status)::text)
  CTE include_list
    ->  Result  (cost=0.00..1.77 rows=100 width=32) (actual time=0.003..0.004 rows=4 loops=1)
          ->  ProjectSet  (cost=0.00..0.52 rows=100 width=32) (actual time=0.002..0.003 rows=4 loops=1)
                ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
  ->  Seq Scan on fact_orders  (cost=0.00..240253.85 rows=3400485 width=38) (actual time=0.006..551.558 rows=3400485 loops=1)
  ->  Hash  (cost=2.00..2.00 rows=100 width=32) (actual time=0.009..0.009 rows=4 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  CTE Scan on include_list  (cost=0.00..2.00 rows=100 width=32) (actual time=0.004..0.007 rows=4 loops=1)
Planning time: 0.163 ms
Execution time: 1852.226 ms

According to the query above, I have an execution time of 1852.226 ms.

There are approximately 3.3 million records returned.

But when I run the query without the EXPLAIN ANALYZE, it takes roughly ~30 seconds to get the results back from my database client.

Is the extra 28 seconds the transfer time from the server to my client? Or is that the actual time to execute the query?

Edit: Client is Navicat. Using the time elapsed after the results are yielded to the screen.

Solution 1:

The documentation says:

Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual.

So the only difference between running an explain on a select query and running the actual query is that data is not actually fetched. Your query returns a huge amount of records, so that only can very well explain the difference that you are seeing.