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;
Update 1:
Turning off sequential scan doesn't seem to help much.
foodb=# set enable_seqscan = off;
SET
foodb=# EXPLAIN ANALYZE SELECT DISTINCT tag FROM datapoints;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.82..591769146.07 rows=25139 width=106) (actual time=0.037..81086.671 rows=218367 loops=1)
-> Index Only Scan using ix_datapoints_tag on datapoints (cost=0.82..591375490.15 rows=157462368 width=106) (actual time=0.033..54393.956 rows=162727709 loops=1)
Heap Fetches: 162727709
Total runtime: 81104.455 ms
(4 rows)
Update 2:
Tried creating an index on time column, and ran the second query, still no luck.
foodb=# CREATE INDEX ix_datapoints_time ON datapoints(time);
CREATE INDEX
foodb=# EXPLAIN ANALYZE SELECT tag, min(time) as minTime, max(time) as maxTime FROM datapoints GROUP BY tag;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=5914895.96..5915147.35 rows=25139 width=114) (actual time=91870.389..91931.164 rows=218367 loops=1)
-> Seq Scan on datapoints (cost=0.00..4694438.12 rows=162727712 width=114) (actual time=0.013..24101.108 rows=162727709 loops=1)
Total runtime: 91944.348 ms
(3 rows)
