Two different execution plan for the same request

<font size="-1"><font face="Arial">Hi,<br /><br /> I've trouble with some SQL request which have different execution
planswhen ran on two different servers. One server is the development environment, the othe rone is th pre-production
env.<br/> Both servers run postgreSQL 8.3.0 on Linux and :<br />  - both databases contains the same data
(pg_dump/pg_restorebetween servers)<br />  - instances have the same configuration parameters<br />  - vaccum and
analyzeis run every day.<br /> The only difference I can see is the hardware. The pre-preoduction env. has more RAM,
moreCPU and a RAID5 disk array through LVM where data are stored. <br /> Performances should be better on the
pre-productionbut unfortunatelly this is not the case.<br /> Below are the execution plan on development env and
pre-production.As you can see pre-production performance are poor, far slower than dev. env !<br /> For information,
enable_seqscanis turned off (some DBA advice). <br /> Also I can get the same execution plan on both environment by
turningoff enable_mergejoin and enable_hashjoin on the pre-production. Then execution matches and performances are much
better.<br/> Could anyone help to guess why both servers don't have the same execution plans ? Can this be due to
hardwaredifference ?<br /><br /> Let me know if you need more detailed informations on these configurations.<br /><br
/>Thanks.<br /><br /> Dev. environment :<br /></font></font><font size="-1"><tt>EXPLAIN analyze SELECT DISTINCT
ConstantesTableNBienService.id,ConstantesTableNBienService.code,ConstantesTableNBienService.lib_code<br/> FROM
T_DEMANDEConstantesTableDemande<br /> LEFT OUTER JOIN  T_OPERATION ConstantesTableOperation<br />     ON
ConstantesTableDemande.id_tech= ConstantesTableOperation.id_demande<br /> LEFT OUTER JOIN T_BIEN_SERVICE
ConstantesTableBienService<br/>     ON  ConstantesTableBienService.id_operation = ConstantesTableOperation.id_tech<br
/>LEFT OUTER JOIN N_BIEN_SERVICE ConstantesTableNBienService<br />     ON ConstantesTableBienService.bs_code =
ConstantesTableNBienService.id<br/> WHERE<br />     ConstantesTableDemande.id_tech = 'y+3eRapRQjW8mtL4wHd4/A=='<br />
   AND ConstantesTableOperation.type_operation = 'acq'<br />     AND ConstantesTableNBienService.parent is null<br />
ORDERBY ConstantesTableNBienService.code ASC;<br />
                                                                                            QUERY
PLAN                                                     <br />
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/> Unique  (cost=3586307.73..3586341.94 rows=205 width=123) (actual time=440.626..440.875 rows=1 loops=1)<br />   
-> Sort  (cost=3586307.73..3586316.28 rows=3421 width=123) (actual time=440.624..440.723 rows=187 loops=1)<br />
        Sort Key: constantestablenbienservice.code, constantestablenbienservice.id,
constantestablenbienservice.lib_code<br/>          Sort Method:  quicksort  Memory: 24kB<br />          ->  Nested
LoopLeft Join  (cost=40.38..3586106.91 rows=3421 width=123) (actual time=71.696..440.240 rows=187 loops=1)<br />
              Filter: (constantestablenbienservice.parent IS NULL)<br />                ->  Nested Loop Left Join 
(cost=40.38..3554085.80rows=6842 width=4) (actual time=66.576..433.797 rows=187 loops=1)<br />                     
-> Nested Loop  (cost=0.00..5041.46 rows=1246 width=25) (actual time=22.923..23.054 rows=30 loops=1)<br />
                          ->  Index Scan using t_demande_pkey on t_demande constantestabledemande  (cost=0.00..8.32
rows=1width=25) (actual time=5.534..5.537 rows=1 loops=1)<br />                                  Index Cond:
((id_tech)::text= 'y+3eRapRQjW8mtL4wHd4/A=='::text)<br />                            ->  Index Scan using
idx_operation_demandeon t_operation constantestableoperation  (cost=0.00..5020.68 rows=1246 width=50) (actual
time=17.382..17.460rows=30 loops=1)<br />                                  Index Cond:
((constantestableoperation.id_demande)::text= 'y+3eRapRQjW8mtL4wHd4/A=='::text)<br />                                 
Filter:((constantestableoperation.type_operation)::text = 'acq'::text)<br />                      ->  Bitmap Heap
Scanon t_bien_service constantestablebienservice  (cost=40.38..2836.96 rows=911 width=29) (actual time=13.511..13.677
rows=6loops=30)<br />                            Recheck Cond: ((constantestablebienservice.id_operation)::text =
(constantestableoperation.id_tech)::text)<br/>                            ->  Bitmap Index Scan on
idx_bien_service_operation (cost=0.00..40.15 rows=911 width=0) (actual time=13.144..13.144 rows=6 loops=30)<br />
                                Index Cond: ((constantestablebienservice.id_operation)::text =
(constantestableoperation.id_tech)::text)<br/>                ->  Index Scan using n_bien_service_pkey on
n_bien_serviceconstantestablenbienservice  (cost=0.00..4.67 rows=1 width=127) (actual time=0.030..0.031 rows=1
loops=187)<br/>                      Index Cond: (constantestablebienservice.bs_code =
constantestablenbienservice.id)<br/><b><font color="#ff0000"> Total runtime: 2.558 ms<br /></font></b>(20
lignes)</tt></font><br/><font size="-1"><font face="Arial"><br /><br /> Pre-production:<br /></font></font><font
size="-1"><tt>EXPLAINanalyze SELECT DISTINCT
ConstantesTableNBienService.id,ConstantesTableNBienService.code,ConstantesTableNBienService.lib_code<br/> FROM
T_DEMANDEConstantesTableDemande<br /> LEFT OUTER JOIN  T_OPERATION ConstantesTableOperation<br />     ON
ConstantesTableDemande.id_tech= ConstantesTableOperation.id_demande<br /> LEFT OUTER JOIN T_BIEN_SERVICE
ConstantesTableBienService<br/>     ON  ConstantesTableBienService.id_operation = ConstantesTableOperation.id_tech<br
/>LEFT OUTER JOIN N_BIEN_SERVICE ConstantesTableNBienService<br />     ON ConstantesTableBienService.bs_code =
ConstantesTableNBienService.id<br/> WHERE<br />     ConstantesTableDemande.id_tech = 'y+3eRapRQjW8mtL4wHd4/A=='<br />
   AND ConstantesTableOperation.type_operation = 'acq'<br />     AND ConstantesTableNBienService.parent is null<br />
ORDERBY ConstantesTableNBienService.code ASC;<br />
                                                                                                       QUERY
PLAN                                          <br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/> Unique  (cost=2679729.52..2679763.24 rows=205 width=123) (actual time=740448.007..740448.269 rows=1 loops=1)<br />
  ->  Sort  (cost=2679729.52..2679737.95 rows=3372 width=123) (actual time=740448.004..740448.111 rows=187
loops=1)<br/>          Sort Key: constantestablenbienservice.code, constantestablenbienservice.id,
constantestablenbienservice.lib_code<br/>          Sort Method:  quicksort  Memory: 24kB<br />          ->  Hash
LeftJoin  (cost=2315662.87..2679531.93 rows=3372 width=123) (actual time=723479.640..740447.597 rows=187 loops=1)<br />
              Hash Cond: (constantestablebienservice.bs_code = constantestablenbienservice.id)<br />               
Filter:(constantestablenbienservice.parent IS NULL)<br />                ->  Hash Left Join 
(cost=2315640.98..2679417.33rows=6743 width=4) (actual time=723464.693..740432.218 rows=187 loops=1)<br />
                    Hash Cond: ((constantestableoperation.id_tech)::text =
(constantestablebienservice.id_operation)::text)<br/>                      ->  Nested Loop  (cost=39.49..4659.51
rows=1228width=25) (actual time=0.131..0.309 rows=30 loops=1)<br />                            ->  Index Scan using
t_demande_pkeyon t_demande constantestabledemande  (cost=0.00..8.32 rows=1 width=25) (actual time=0.047..0.050 rows=1
loops=1)<br/>                                  Index Cond: ((id_tech)::text = 'y+3eRapRQjW8mtL4wHd4/A=='::text)<br />
                          ->  Bitmap Heap Scan on t_operation constantestableoperation  (cost=39.49..4638.90
rows=1228width=50) (actual time=0.079..0.192 rows=30 loops=1)<br />                                  Recheck Cond:
((constantestableoperation.id_demande)::text= 'y+3eRapRQjW8mtL4wHd4/A=='::text)<br />                                 
Filter:((constantestableoperation.type_operation)::text = 'acq'::text)<br />                                  -> 
BitmapIndex Scan on idx_operation_demande  (cost=0.00..39.18 rows=1228 width=0) (actual time=0.061..0.061 rows=30
loops=1)<br/>                                        Index Cond: ((constantestableoperation.id_demande)::text =
'y+3eRapRQjW8mtL4wHd4/A=='::text)<br/>                      ->  Hash  (cost=1486192.10..1486192.10 rows=42894672
width=29)(actual time=723119.538..723119.538 rows=42894671 loops=1)<br />                            ->  Index Scan
usingidx_bien_service_code on t_bien_service constantestablebienservice  (cost=0.00..1486192.10 rows=42894672 width=29)
(actualtime=21.546..671603.500 rows=42894671 loops=1)<br />                ->  Hash  (cost=19.33..19.33 rows=205
width=127)(actual time=14.706..14.706 rows=205 loops=1)<br />                      ->  Index Scan using
n_bien_service_pkeyon n_bien_service constantestablenbienservice  (cost=0.00..19.33 rows=205 width=127) (actual
time=10.262..14.401rows=205 loops=1)<br /><b><font color="#ff0000"> Total runtime: 740465.922 ms</font></b><br /> (22
lignes)<br/></tt></font><font size="-1"><font face="Arial"><br /></font></font>