PosgreSQL: very slow select on a single table with no joins I'm trying to help a colleague who's having some problems with a PostgreSQL query.

We've narrowed it down to a join to one table making the query painfully slow and, upon investigation, we found that even a simple select * request on the "data" table takes up to 2 minutes to return the results.

The table contains 420,000 records and 13 columns, none of which are BLOBs or anything massive (they're all text).

Running an EXPLAIN ANALYSE VERBOSE massively underestimates the time taken to return the results

i.e. for SELECT * FROM DATA

"Seq Scan on data  (cost=0.00..18284.35 rows=389735 width=27) 
(actual time=0.135..594.929 rows=426934 loops=1)"
"  Output: id,..."
"Total runtime: 1069.643 ms"


Whereas running the query without EXPLAIN gives

Total query runtime: 86598 ms.
426934 rows retrieved.


I'm sorry if I'm breaking any conventions in asking the question but I don't really know Postgres at all so if anyone could suggest what kind of underlying issues we should be looking for to help resolve this issue, it would be gratefully received.

Edit: as requested, the table definition

CREATE TABLE data
(
  id text NOT NULL,
  methodid text NOT NULL,
  replicateid text, 
  t_id text NOT NULL, 
  t_name text NOT NULL, 
  a_id text NOT NULL, 
  originalname text,
  qualifier text,
  frequency text,
  frequencyunits text NOT NULL, 
  determiner text,
  sensitive text,
  versionkey text,
  CONSTRAINT data_pkey PRIMARY KEY (id),
  CONSTRAINT sample FOREIGN KEY (sid)
      REFERENCES sample (sid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE INDEX eventid_idx
  ON data
  USING btree
  (id);
Running an EXPLAIN ANALYSE VERBOSE massively underestimates the time
  taken to return the results


There's a misunderstanding here, because EXPLAIN ANALYZE does not estimate, it runs the query for real and reports the actual time taken by each steps, as opposed to EXPLAIN without ANALYZE that just reports the estimates without running the query.

Consider this line from your EXPLAIN ANALYZE output:


  (actual time=0.135..594.929 rows=426934 loops=1)


These 595 milliseconds are not an estimate, it's actual time. Besides, when postgres shows estimates, they're expressed in units of "cost", not in units of time.

When it's finished, EXPLAIN ANALYZE sends back the result of the analyze to the client, and discards the rows of the actual result. That differs from a real SELECT which has to send back the rows to the client.

For this reason, the big difference between both operations in your case could/should be accounted by the slowness in receiving the results, either because the network is slow, or because the client is slow, or both. Maybe the client is swapping like crazy if the resultset is too big to fit in the available RAM. I'd look at the vmstat 1 and iftop outputs (or even strace) on server and client during these 2 minutes to check what's doing what.