Two different execution plan for the same request - Mailing list pgsql-performance
| From | JOUANIN Nicolas (44) |
|---|---|
| Subject | Two different execution plan for the same request |
| Date | |
| Msg-id | 4C3432F0.1090004@dgfip.finances.gouv.fr Whole thread Raw |
| Responses |
Re: Two different execution plan for the same request
|
| List | pgsql-performance |
<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>
pgsql-performance by date: