I'm not very experienced with database query optimization. I've been reading through similar questions here and Postgres tuning articles online, but unfortunately I haven't had any luck. Here's what I have (DB version, table information, number of rows, EXPLAIN ANALYZE
of the two queries I would like to optimize):
foodb=# SELECT version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
(1 row)
foodb=# \d+ datapoints
Table "public.datapoints"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+--------------+-------------
tag | character varying(200) | not null | extended | |
time | timestamp without time zone | not null | plain | |
value | double precision | | plain | |
Indexes:
"ix_datapoints_tag" btree (tag)
"ix_datapoints_tag_time_value" btree (tag, "time", value)
Has OIDs: no
foodb=# SELECT COUNT(*) FROM datapoints;
count
-----------
162727709
(1 row)
foodb=# EXPLAIN ANALYZE SELECT DISTINCT tag FROM datapoints;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=5035440.60..5035691.99 rows=25139 width=106) (actual time=74304.278..74357.708 rows=218367 loops=1)
-> Seq Scan on datapoints (cost=0.00..4641784.68 rows=157462368 width=106) (actual time=0.019..24484.872 rows=162727709 loops=1)
Total runtime: 74371.153 ms
(3 rows)
foodb=# EXPLAIN ANALYZE SELECT tag, min(time) as minTime, max(time) as maxTime FROM datapoints GROUP BY tag;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=5822752.44..5823003.83 rows=25139 width=114) (actual time=86848.009..86908.264 rows=218367 loops=1)
-> Seq Scan on datapoints (cost=0.00..4641784.68 rows=157462368 width=114) (actual time=0.010..19226.031 rows=162727709 loops=1)
Total runtime: 86922.564 ms
(3 rows)
I tried to make the following configuration changes in postgresql.conf
, but couldn't see much performance improvement:
max_connections = 128 // was 300
work_mem = 64m // was 8m
shared_buffers = 4096m // was 128m
effective_cache_size = 4096m // was 128m
What do you suggest so that I can improve the performance of these two queries?
SELECT DISTINCT tag FROM datapoints;
SELECT tag, min(time) as minTime, max(time) as maxTime FROM datapoints GROUP BY tag;