-1

I'm trying to run a SELECT query on our PostgreSQL database using pgAdmin 3. It looks like this:

SELECT
    tstamp AS "time"
    dvalue AS "Weight"
  FROM tablename
  WHERE
    tstamp BETWEEN 'time1' AND 'time2' AND description = "exp_weight"
  ORDER BY 1

Our database tracks metrics for a lot more than just weight so we have about 30 million rows to parse in order to find all the exp_weight rows. When I run this, it takes about a minute and a half to aggregate all of the data. Any idea how I can speed this up?

Considerations

  • I'm running PostgreSQL 10.
  • I've improved performance by adding a multicolumn index on tstamp, description, and dvalue which brings the runtime down to ~30 seconds. Ideally, I'd like to get this to less than a second.

  • I've optimized the query a bit

    SELECT
        tstamp AS "time"
        dvalue AS "Weight"   
    FROM tablename   
    WHERE
        description = "exp_weight" AND tstamp BETWEEN 'time1' AND 'time2'   
    ORDER BY tstamp
    

The output to EXPLAIN (ANALYZE, BUFFERS) is

Index Only Scan using tablename_description_tstamp_dvalue_idx on tablename (cost=0.56..76427.86 rows=1536760 width=16) (actual time=1.002..435.999 rows=1566102 loops=1)"
  Index Cond: ((description = 'exp_weight'::text) AND (tstamp >= '2019-03-17 16:21:57.305-05'::timestamp with time zone) AND (tstamp <= '2019-09-16 16:21:57.305-05'::timestamp with time zone))
  Heap Fetches: 0
  Buffers: shared hit=193 read=11212
  I/O Timings: read=150.105
Planning time: 0.132 ms
Execution time: 474.418 ms

But when I actually run the query it takes 30 seconds.

I've changed some parameters in the config file to allow higher shared_buffers and CPU usage

  • 1
    Do you have any indexes on the table? Particularly covering description and tstamp? – Greg Sep 13 at 2:04
  • What part (amount or percent) of table records does this query selects? What part will be selected while using these conditions separately? And show full table's DDL, including indices. – Akina Sep 13 at 4:36
  • 1
    Most important, show EXPLAIN (ANALYZE, BUFFERS) output for the query. – Laurenz Albe Sep 13 at 7:42
  • 1
    Please consider reading this advice – mustaccio Sep 15 at 15:22
0

Sending 1,566,102 rows from the database to your client (possibly over a network) takes time, and then your client needs to parse them and figure out how to render them.

If it is much faster when you take the client out of the picture, then you now know where the bottleneck is.

  • By client, do you mean the pgAdmin3 front end? – Jake Lyle Sep 18 at 20:49
  • 1
    Yes. And pgAdmin3 is known to be slow in such situations. But anything can be slow if you cram enough stuff into it. – jjanes Sep 18 at 21:43

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.