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)