I have a table which contains 300.000 rows.

I am trying to select * from schema.table from pgAdmin and psql.

Execution time from the pgAdmin:

 explain analyze select * from schema.activity;
"Seq Scan on activity  (cost=0.00..23592.96 rows=305196 width=3497) (actual time=0.009..50.741 rows=304646 loops=1)"
"Planning time: 0.432 ms"
"Execution time: 60.054 ms"

But the actual select * from schema.activity; is taking 4 minutes.

Same from the psql from the server using local connection:

\timing on
select * from schema.table;
Time: 1279.901 ms
OnlineGIS=# explain analyze select * from schema.table;
                                                    QUERY PLAN
 Seq Scan on manhole  (cost=0.00..23619.37 rows=305537 width=3497) (actual time=0.009..44.783 rows=304647 loops=1)
 Planning time: 0.230 ms
 Execution time: 54.094 ms
(3 rows)

Time: 54.865 ms

Could you please suggest:

  1. What might be the reason to showing different timings with explain analyze and actual select * from schema.table?
  2. How to troubleshoot these issues?
  • 2
    You are sending 1GB of data over the network. That takes some time. And pgAdmin is known to be extremely slow when displaying many rows. Even "thousands" is too many of pgAdmin, let alone 300.000 – a_horse_with_no_name Sep 13 '19 at 13:04