|
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) |
