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?
- I'm running PostgreSQL 10.
I've improved performance by adding a multicolumn index on
dvaluewhich 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