2

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);
  • Will you show us the problematic query? – ypercubeᵀᴹ Aug 11 '14 at 15:57
  • @ypercube sure, it's just "select * from data" – Ambulare Aug 11 '14 at 15:58
  • Oh, I meant the 1st one, with the joins. A select * on a 420K rows will surely take some time. If your question is only about this table (and the select * query), add the table definition. – ypercubeᵀᴹ Aug 11 '14 at 15:59
  • @ypercube - I'm most curious about just the select * on this table. 2 minutes seems a very long time for 400,000 rows with no operations or joins being performed. – Ambulare Aug 11 '14 at 16:06
  • 3
    Sending a result set over the network and rendering it on the client takes time. Instead of SELECT * FROM, try running SELECT SUM(LENGTH(id)) FROM - that might run faster. – A-K Aug 11 '14 at 16:46
6

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.

  • Thank you (and the others above) for your response. I believe you are correct and that the issue here is the volume of data being returned rather than the query speed itself. I will take steps to correct this issue. – Ambulare Aug 13 '14 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.