The query have been running ok for some time now, but this morning I
decided to run vacuum analyze (vacuumdb -a -z) on the cluster, and
suddenly the query isn't running very well at all. This query has only
one value in the "IN", if I add another id the query becomes really
really slow.
Query:
SELECT
data.entered,
data.machine_id,
datatemplate_intervals.template_id,
data_values.value
FROM
data, data_values, datatemplate_intervals
WHERE
datatemplate_intervals.id = data_values.template_id AND
data_values.data_id = data.id AND
data.machine_id IN (2) AND
current_timestamp::timestamp - interval '60 seconds' < data.entered
Indexes exists on data_values.template_id, data.entered,
data.machine_id, datatemplate_intervals.machine_id,
datatemplate_intervals.template_id.
Data contains almost 1.5milj entries, and data_values around 9.1milj. As
I write this letter I check the tables in pgAdmin, and it tells me this
for table data """
Rows (estimated) 1
Rows (counted) 1491401
""" even though I run vacuum analyze on the table itself from pgadmin.
Explain analyze result attached as explain-analyze.txt
Explain without analyze when using IN(2,3) attached as explain.txt
Regards,
Robin
Hash Join (cost=1.93..175359.44 rows=152524 width=24) (actual time=0.972..1.085
rows=5 loops=1)
Hash Cond: ("outer".template_id = "inner".id)
-> Nested Loop (cost=0.00..173069.64 rows=152524 width=24) (actual
time=0.274..0.358 rows=5 loops=1)
-> Index Scan using idx_d_entered on data (cost=0.00..18024.04 rows=50360
width=16) (actual time=0.210..0.247 rows=1 loops=1)
Index Cond: (((('now'::text)::timestamp(6) with time zone)::timestamp
without time zone - '00:01:00'::interval) < entered)
Filter: (machine_id = 2)
-> Index Scan using idx_dv_data_id on data_values (cost=0.00..3.04 rows=3
width=16) (actual time=0.049..0.074 rows=5 loops=1)
Index Cond: (data_values.data_id = "outer".id)
-> Hash (cost=1.74..1.74 rows=74 width=8) (actual time=0.438..0.438 rows=0
loops=1)
-> Seq Scan on datatemplate_intervals (cost=0.00..1.74 rows=74 width=8)
(actual time=0.028..0.255 rows=74 loops=1)
Total runtime: 1.575 ms
(11 rows)
Hash Join (cost=27010.45..254388.49 rows=253820 width=24)
Hash Cond: ("outer".template_id = "inner".id)
-> Merge Join (cost=27008.52..250579.25 rows=253820 width=24)
Merge Cond: ("outer".data_id = "inner".id)
-> Index Scan using idx_dv_data_id on data_values (cost=0.00..197001.44
rows=9127404 width=16)
-> Sort (cost=27008.52..27218.04 rows=83806 width=16)
Sort Key: data.id
-> Index Scan using idx_d_entered on data (cost=0.00..19266.46
rows=83806 width=16)
Index Cond: (((('now'::text)::timestamp(6) with time
zone)::timestamp without time zone - '00:01:00'::interval) < entered)
Filter: ((machine_id = 2) OR (machine_id = 3))
-> Hash (cost=1.74..1.74 rows=74 width=8)
-> Seq Scan on datatemplate_intervals (cost=0.00..1.74 rows=74 width=8)
(12 rows)
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
