Change query join order

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)