Message-ID: <4BBDD2F3.7030701@digsys.bg>
Date: Thu, 08 Apr 2010 15:58:27 +0300
From: Kaloyan Iliev Iliev <kaloyan@digsys.bg>
Reply-To: kaloyan@digsys.bg
Organization: Register.BG Ltd.
User-Agent: Thunderbird 2.0.0.23 (X11/20091015)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Query Optimization
Content-Type: text/plain; charset=windows-1251; format=flowed
Content-Transfer-Encoding: 7bit

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