Query Optimization

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux