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

Question

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.

Dayna

Which client are you using, and how do you measure the time? – Cephalometer

I'm using Navicat. And the time is the "time elapsed" shown once the client yields the results to the screen. @LaurenzAlbe – Dayna

Did the EXPLAIN ANALYZE take 30 to get to the screen, or the SELECT...? I'm asking bc maybe it's the volume of data that needs to get ingested into Navicat – Basophil

No the EXPLAIN ANALYZE was instant. – Dayna

Answer 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.

Khalilahkhalin

When running EXPLAIN ANALYZE on a query that takes a couple of milliseconds it outputs that it took 10 seconds to execute it. I tried running VACUUM table_name but that didn't help. I'm using PostgreSQL v.16.1 with pAdmin4 v8.1. What's wrong with it, any ideas ? – Seigniory