Thread: Query Optimization
Hi,
Can anyone suggest why this query so slow.
SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.2 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC)
4.2.1 20070719 [FreeBSD], 32-bit
(1 row)
explain analyze SELECT DT.value,
DT.meassure_date,
DT.ms_status_id as status_id,
S.descr_bg as status_bg,
S.descr_en as status_en,
VT.id as value_type_id,
VT.descr_en as value_type_en,
VT.descr_bg as value_type_bg,
T.unit as value_type_unit,
T.name as general_value_type,
T.ms_db_type_id
FROM
ms_data AS DT,
ms_statuses AS S,
ms_value_types AS VT,
ms_types AS T,
ms_commands_history AS CH
WHERE DT.ms_value_type_id = 88 AND
DT.meassure_date >= '2010-04-01 1:00:00' AND
DT.meassure_date <= '2010-04-01 1:10:00' AND
DT.ms_command_history_id = CH.id AND
CH.ms_device_id = 7 AND
DT.ms_value_type_id = VT.id AND
VT.ms_type_id = T.id AND
DT.ms_status_id = S.id
GROUP BY value,
meassure_date,
status_id,
status_bg,
status_en,
value_type_id,
value_type_en,
value_type_bg,
value_type_unit,
general_value_type,
ms_db_type_id
ORDER BY meassure_date DESC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Group (cost=23.93..23.96 rows=1 width=229) (actual
time=63274.021..63274.021 rows=0
loops=1)
-> Sort (cost=23.93..23.94 rows=1 width=229) (actual
time=63274.016..63274.016 rows=0 loops=1)
Sort Key: dt.meassure_date, dt.value, dt.ms_status_id, s.descr_bg,
s.descr_en, vt.id, vt.descr_en, vt.descr_bg, t.unit, t.name,
t.ms_db_type_id
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=0.00..23.92 rows=1 width=229) (actual
time=63273.982..63273.982 rows=0
loops=1)
-> Nested Loop (cost=0.00..19.64 rows=1 width=165) (actual
time=63273.977..63273.977 rows=0
loops=1)
-> Nested Loop (cost=0.00..15.36 rows=1 width=101)
(actual time=63273.974..63273.974 rows=0
loops=1)
-> Nested Loop (cost=0.00..11.08 rows=1 width=23)
(actual time=63273.970..63273.970 rows=0
loops=1)
-> Index Scan using
ms_commands_history_ms_device_id_idx on ms_commands_history ch
(cost=0.00..4.33 rows=1 width=8) (actual time=0.163..25254.004 rows=9807
loops=1)
Index Cond: (ms_device_id = 7)
-> Index Scan using
ms_data_ms_command_history_id_idx on ms_data dt (cost=0.00..6.74 rows=1
width=31) (actual time=3.868..3.868 rows=0 loops=9807)
Index Cond:
(dt.ms_command_history_id = ch.id)
Filter: ((dt.meassure_date >=
'2010-04-01 01:00:00'::timestamp without time zone) AND
(dt.meassure_date <= '2010-04-01 01:10:00'::timestamp without time zone)
AND (dt.ms_value_type_id = 88))
-> Index Scan using ms_value_types_pkey on
ms_value_types vt (cost=0.00..4.27 rows=1 width=82) (never
executed) Index Cond:
(vt.id = 88)
-> Index Scan using ms_types_pkey on ms_types t
(cost=0.00..4.27 rows=1 width=72) (never
executed)
Index Cond: (t.id = vt.ms_type_id)
-> Index Scan using ms_statuses_pkey on ms_statuses s
(cost=0.00..4.27 rows=1 width=68) (never
executed) Index
Cond: (s.id = dt.ms_status_id)
Total runtime: 63274.256 ms
Thanks in advance.
Kaloyan Iliev
Sorry for the noise. I make vacuum analyze and the problem is solved. Kaloyan Iliev Kaloyan Iliev Iliev wrote: > Hi, > Can anyone suggest why this query so slow. > > SELECT version(); > version > --------------------------------------------------------------------------------------------------------- > > PostgreSQL 8.4.2 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC) > 4.2.1 20070719 [FreeBSD], 32-bit > (1 row) > explain analyze SELECT DT.value, > DT.meassure_date, > DT.ms_status_id as status_id, > S.descr_bg as status_bg, > S.descr_en as status_en, > VT.id as value_type_id, > VT.descr_en as value_type_en, > VT.descr_bg as value_type_bg, > T.unit as value_type_unit, > T.name as general_value_type, > T.ms_db_type_id > FROM > ms_data AS DT, > ms_statuses AS S, > ms_value_types AS VT, > ms_types AS T, > ms_commands_history AS CH > WHERE DT.ms_value_type_id = 88 AND > DT.meassure_date >= '2010-04-01 1:00:00' AND > DT.meassure_date <= '2010-04-01 1:10:00' AND > DT.ms_command_history_id = CH.id AND > CH.ms_device_id = 7 AND > DT.ms_value_type_id = VT.id AND > VT.ms_type_id = T.id AND > DT.ms_status_id = S.id > GROUP BY value, > meassure_date, > status_id, > status_bg, > status_en, > value_type_id, > value_type_en, > value_type_bg, > value_type_unit, > general_value_type, > ms_db_type_id > ORDER BY meassure_date DESC; > QUERY PLAN > -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > Group (cost=23.93..23.96 rows=1 width=229) (actual > time=63274.021..63274.021 rows=0 > loops=1) > -> Sort (cost=23.93..23.94 rows=1 width=229) (actual > time=63274.016..63274.016 rows=0 loops=1) > Sort Key: dt.meassure_date, dt.value, dt.ms_status_id, s.descr_bg, > s.descr_en, vt.id, vt.descr_en, vt.descr_bg, t.unit, t.name, > t.ms_db_type_id Sort Method: quicksort Memory: 17kB > -> Nested Loop (cost=0.00..23.92 rows=1 width=229) (actual > time=63273.982..63273.982 rows=0 > loops=1) > -> Nested Loop (cost=0.00..19.64 rows=1 width=165) > (actual time=63273.977..63273.977 rows=0 > loops=1) > -> Nested Loop (cost=0.00..15.36 rows=1 width=101) > (actual time=63273.974..63273.974 rows=0 > loops=1) > -> Nested Loop (cost=0.00..11.08 rows=1 width=23) > (actual time=63273.970..63273.970 rows=0 > loops=1) > -> Index Scan using > ms_commands_history_ms_device_id_idx on ms_commands_history ch > (cost=0.00..4.33 rows=1 width=8) (actual time=0.163..25254.004 > rows=9807 loops=1) > Index Cond: (ms_device_id = 7) > -> Index Scan using > ms_data_ms_command_history_id_idx on ms_data dt (cost=0.00..6.74 > rows=1 width=31) (actual time=3.868..3.868 rows=0 loops=9807) > Index Cond: > (dt.ms_command_history_id = ch.id) > Filter: ((dt.meassure_date >= > '2010-04-01 01:00:00'::timestamp without time zone) AND > (dt.meassure_date <= '2010-04-01 01:10:00'::timestamp without time > zone) AND (dt.ms_value_type_id = 88)) > -> Index Scan using ms_value_types_pkey on > ms_value_types vt (cost=0.00..4.27 rows=1 width=82) (never > executed) Index Cond: > (vt.id = 88) > -> Index Scan using ms_types_pkey on ms_types t > (cost=0.00..4.27 rows=1 width=72) (never > executed) > Index Cond: (t.id = vt.ms_type_id) > -> Index Scan using ms_statuses_pkey on ms_statuses s > (cost=0.00..4.27 rows=1 width=68) (never > executed) Index > Cond: (s.id = dt.ms_status_id) > Total runtime: 63274.256 ms > Thanks in advance. > > Kaloyan Iliev >
In response to Kaloyan Iliev Iliev : > Hi, > Can anyone suggest why this query so slow. > -> Index Scan using > ms_commands_history_ms_device_id_idx on ms_commands_history ch > (cost=0.00..4.33 rows=1 width=8) (actual time=0.163..25254.004 rows=9807 > loops=1) Estimated rows: 1, actual rows: 9807, that's a BIG difference and, maybe, your problem. Btw.: your explain is hard to read (line-wrapping). It's better to attach the explain as an own file... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99