Hi,

I've trouble with some SQL request which have different execution plans when ran on two different servers. One server is the development environment, the othe rone is th pre-production env.
Both servers run postgreSQL 8.3.0 on Linux and :
 - both databases contains the same data (pg_dump/pg_restore between servers)
 - instances have the same configuration parameters
 - vaccum and analyze is run every day.
The only difference I can see is the hardware. The pre-preoduction env. has more RAM, more CPU and a RAID5 disk array through LVM where data are stored.
Performances should be better on the pre-production but unfortunatelly this is not the case.
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 !
For information, enable_seqscan is turned off (some DBA advice).
Also I can get the same execution plan on both environment by turning off enable_mergejoin and enable_hashjoin on the pre-production. Then execution matches and performances are much better.
Could anyone help to guess why both servers don't have the same execution plans ? Can this be due to hardware difference ?

Let me know if you need more detailed informations on these configurations.

Thanks.

Dev. environment :
EXPLAIN analyze SELECT DISTINCT ConstantesTableNBienService.id,ConstantesTableNBienService.code,ConstantesTableNBienService.lib_code
FROM T_DEMANDE ConstantesTableDemande
LEFT OUTER JOIN  T_OPERATION ConstantesTableOperation
    ON ConstantesTableDemande.id_tech = ConstantesTableOperation.id_demande
LEFT OUTER JOIN T_BIEN_SERVICE ConstantesTableBienService
    ON  ConstantesTableBienService.id_operation = ConstantesTableOperation.id_tech
LEFT OUTER JOIN N_BIEN_SERVICE ConstantesTableNBienService
    ON ConstantesTableBienService.bs_code = ConstantesTableNBienService.id
WHERE
    ConstantesTableDemande.id_tech = 'y+3eRapRQjW8mtL4wHd4/A=='
    AND ConstantesTableOperation.type_operation = 'acq'
    AND ConstantesTableNBienService.parent is null
ORDER BY ConstantesTableNBienService.code ASC;
                                                                                             QUERY PLAN                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=3586307.73..3586341.94 rows=205 width=123) (actual time=440.626..440.875 rows=1 loops=1)
   ->  Sort  (cost=3586307.73..3586316.28 rows=3421 width=123) (actual time=440.624..440.723 rows=187 loops=1)
         Sort Key: constantestablenbienservice.code, constantestablenbienservice.id, constantestablenbienservice.lib_code
         Sort Method:  quicksort  Memory: 24kB
         ->  Nested Loop Left Join  (cost=40.38..3586106.91 rows=3421 width=123) (actual time=71.696..440.240 rows=187 loops=1)
               Filter: (constantestablenbienservice.parent IS NULL)
               ->  Nested Loop Left Join  (cost=40.38..3554085.80 rows=6842 width=4) (actual time=66.576..433.797 rows=187 loops=1)
                     ->  Nested Loop  (cost=0.00..5041.46 rows=1246 width=25) (actual time=22.923..23.054 rows=30 loops=1)
                           ->  Index Scan using t_demande_pkey on t_demande constantestabledemande  (cost=0.00..8.32 rows=1 width=25) (actual time=5.534..5.537 rows=1 loops=1)
                                 Index Cond: ((id_tech)::text = 'y+3eRapRQjW8mtL4wHd4/A=='::text)
                           ->  Index Scan using idx_operation_demande on t_operation constantestableoperation  (cost=0.00..5020.68 rows=1246 width=50) (actual time=17.382..17.460 rows=30 loops=1)
                                 Index Cond: ((constantestableoperation.id_demande)::text = 'y+3eRapRQjW8mtL4wHd4/A=='::text)
                                 Filter: ((constantestableoperation.type_operation)::text = 'acq'::text)
                     ->  Bitmap Heap Scan on t_bien_service constantestablebienservice  (cost=40.38..2836.96 rows=911 width=29) (actual time=13.511..13.677 rows=6 loops=30)
                           Recheck Cond: ((constantestablebienservice.id_operation)::text = (constantestableoperation.id_tech)::text)
                           ->  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)
                                 Index Cond: ((constantestablebienservice.id_operation)::text = (constantestableoperation.id_tech)::text)
               ->  Index Scan using n_bien_service_pkey on n_bien_service constantestablenbienservice  (cost=0.00..4.67 rows=1 width=127) (actual time=0.030..0.031 rows=1 loops=187)
                     Index Cond: (constantestablebienservice.bs_code = constantestablenbienservice.id)
 Total runtime: 2.558 ms
(20 lignes)


Pre-production:
EXPLAIN analyze SELECT DISTINCT ConstantesTableNBienService.id,ConstantesTableNBienService.code,ConstantesTableNBienService.lib_code
FROM T_DEMANDE ConstantesTableDemande
LEFT OUTER JOIN  T_OPERATION ConstantesTableOperation
    ON ConstantesTableDemande.id_tech = ConstantesTableOperation.id_demande
LEFT OUTER JOIN T_BIEN_SERVICE ConstantesTableBienService
    ON  ConstantesTableBienService.id_operation = ConstantesTableOperation.id_tech
LEFT OUTER JOIN N_BIEN_SERVICE ConstantesTableNBienService
    ON ConstantesTableBienService.bs_code = ConstantesTableNBienService.id
WHERE
    ConstantesTableDemande.id_tech = 'y+3eRapRQjW8mtL4wHd4/A=='
    AND ConstantesTableOperation.type_operation = 'acq'
    AND ConstantesTableNBienService.parent is null
ORDER BY ConstantesTableNBienService.code ASC;
                                                                                                        QUERY PLAN                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=2679729.52..2679763.24 rows=205 width=123) (actual time=740448.007..740448.269 rows=1 loops=1)
   ->  Sort  (cost=2679729.52..2679737.95 rows=3372 width=123) (actual time=740448.004..740448.111 rows=187 loops=1)
         Sort Key: constantestablenbienservice.code, constantestablenbienservice.id, constantestablenbienservice.lib_code
         Sort Method:  quicksort  Memory: 24kB
         ->  Hash Left Join  (cost=2315662.87..2679531.93 rows=3372 width=123) (actual time=723479.640..740447.597 rows=187 loops=1)
               Hash Cond: (constantestablebienservice.bs_code = constantestablenbienservice.id)
               Filter: (constantestablenbienservice.parent IS NULL)
               ->  Hash Left Join  (cost=2315640.98..2679417.33 rows=6743 width=4) (actual time=723464.693..740432.218 rows=187 loops=1)
                     Hash Cond: ((constantestableoperation.id_tech)::text = (constantestablebienservice.id_operation)::text)
                     ->  Nested Loop  (cost=39.49..4659.51 rows=1228 width=25) (actual time=0.131..0.309 rows=30 loops=1)
                           ->  Index Scan using t_demande_pkey on t_demande constantestabledemande  (cost=0.00..8.32 rows=1 width=25) (actual time=0.047..0.050 rows=1 loops=1)
                                 Index Cond: ((id_tech)::text = 'y+3eRapRQjW8mtL4wHd4/A=='::text)
                           ->  Bitmap Heap Scan on t_operation constantestableoperation  (cost=39.49..4638.90 rows=1228 width=50) (actual time=0.079..0.192 rows=30 loops=1)
                                 Recheck Cond: ((constantestableoperation.id_demande)::text = 'y+3eRapRQjW8mtL4wHd4/A=='::text)
                                 Filter: ((constantestableoperation.type_operation)::text = 'acq'::text)
                                 ->  Bitmap Index Scan on idx_operation_demande  (cost=0.00..39.18 rows=1228 width=0) (actual time=0.061..0.061 rows=30 loops=1)
                                       Index Cond: ((constantestableoperation.id_demande)::text = 'y+3eRapRQjW8mtL4wHd4/A=='::text)
                     ->  Hash  (cost=1486192.10..1486192.10 rows=42894672 width=29) (actual time=723119.538..723119.538 rows=42894671 loops=1)
                           ->  Index Scan using idx_bien_service_code on t_bien_service constantestablebienservice  (cost=0.00..1486192.10 rows=42894672 width=29) (actual time=21.546..671603.500 rows=42894671 loops=1)
               ->  Hash  (cost=19.33..19.33 rows=205 width=127) (actual time=14.706..14.706 rows=205 loops=1)
                     ->  Index Scan using n_bien_service_pkey on n_bien_service constantestablenbienservice  (cost=0.00..19.33 rows=205 width=127) (actual time=10.262..14.401 rows=205 loops=1)
 Total runtime: 740465.922 ms
(22 lignes)