Message-ID: <4B47722A.4010502@digsys.bg>
Date: Fri, 08 Jan 2010 19:58:02 +0200
From: Kaloyan Iliev Iliev <kaloyan@digsys.bg>
User-Agent: Thunderbird 2.0.0.23 (X11/20091015)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Change query join order
Content-Type: text/plain; charset=windows-1251; format=flowed
Content-Transfer-Encoding: 7bit

Hi ,

I have a simple query with two tables.
ms_data ~ 4500000 rows
ms_commands_history ~ 500000 rows

I have done analyze and there are indexes.
My question is why the planner didn't do the index scan first on ms_data 
to reduce the rows to ~ 11000 and the use the PK index on 
ms_commands_history.

Now, if I red the explain correctly it first do the seq_scan on 
ms_commands_history the then the index scan on ms_data.

Any Ideas?

Thanks in advance.

Kaloyan Iliev

SELECT version();
                                                version
--------------------------------------------------------------------------------------------------------- 

PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC) 
4.2.1 20070719  [FreeBSD], 32-bit
(1 row)


explain analyze SELECT COUNT(*) as count
                                               FROM
                                                         ms_data AS DT,
                                                         
ms_commands_history AS CH
                                              WHERE  
DT.ms_command_history_id = CH.id AND
                                                     CH.ms_device_id = 1 
AND
                                                     DT.ms_value_type_id 
= 1  AND
                                                     
DT.meassure_date::date >= '2010-01-01' AND
                                                     
DT.meassure_date::date <= '2010-01-08';


QUERY 
PLAN                                                                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------ 

Aggregate  (cost=88778.73..88778.74 rows=1 width=0) (actual 
time=16979.109..16979.112 rows=1 loops=1)
  ->  Hash Join  (cost=63056.45..88750.77 rows=11183 width=0) (actual 
time=13774.132..16958.507 rows=11093 loops=1)
        Hash Cond: (dt.ms_command_history_id = ch.id)
        ->  Index Scan using ms_data_meassure_date_idx on ms_data dt  
(cost=0.01..23485.68 rows=11183 width=8) (actual time=58.869..2701.928 
rows=11093 loops=1)
              Index Cond: (((meassure_date)::date >= '2010-01-01'::date) 
AND ((meassure_date)::date <= '2010-01-08'::date))
              Filter: (ms_value_type_id = 1)
        ->  Hash  (cost=55149.22..55149.22 rows=481938 width=8) (actual 
time=13590.853..13590.853 rows=481040 loops=1)
              ->  Seq Scan on ms_commands_history ch  
(cost=0.00..55149.22 rows=481938 width=8) (actual time=0.078..12321.037 
rows=481040 loops=1)
                    Filter: (ms_device_id = 1)
Total runtime: 16979.326 ms
(10 rows)