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);