Re: Two different execution plan for the same request
От | JOUANIN Nicolas (44) |
---|---|
Тема | Re: Two different execution plan for the same request |
Дата | |
Msg-id | 4C343F31.2050402@dgfip.finances.gouv.fr обсуждение исходный текст |
Ответ на | Re: Two different execution plan for the same request (Yeb Havinga <yebhavinga@gmail.com>) |
Ответы |
Re: Two different execution plan for the same request
(Yeb Havinga <yebhavinga@gmail.com>)
Re: Two different execution plan for the same request (Guillaume Smet <guillaume.smet@gmail.com>) |
Список | pgsql-performance |
<font size="-1"><font face="Arial">Please find attached schema of tables and indexes involved.<br /> Here is the pre-prod.environment config file:<br /> listen_addresses = '*'<br /> max_connections = 200<br /> shared_buffers = 1024MB<br/> work_mem = 24MB<br /> maintenance_work_mem = 128MB<br /> max_stack_depth = 6MB<br /> max_fsm_pages = 204800<br/> wal_buffers = 921MB<br /> checkpoint_segments = 10<br /> checkpoint_completion_target = 0.9<br /> archive_mode= on<br /> archive_command = 'cp -i %p /postgres/INST1/backup_xlog/%f'<br /> enable_seqscan = off<br /> random_page_cost= 4.0<br /> effective_cache_size = 1536MB<br /> log_destination = 'stderr'<br /> logging_collector = on<br/> log_directory = 'pg_log'<br /> log_truncate_on_rotation = on<br /> log_rotation_age = 1d<br /> log_rotation_size= 0<br /> log_min_duration_statement = 5000<br /> log_duration = on<br /> log_line_prefix='%t - user=%u,db=%d,sess=%c,proc=%p'<br /> log_statement = 'all'<br /> datestyle = 'iso, dmy'<br /> lc_messages = 'C'<br /> lc_monetary= 'C'<br /> lc_numeric = 'C'<br /> lc_time = 'C'<br /> default_text_search_config = 'pg_catalog.english'<br /><br/> The development environment has :<br /> listen_addresses = '*'<br /> max_connections = 200<br /> shared_buffers =1024MB<br /> work_mem = 24MB<br /> maintenance_work_mem = 128MB<br /> max_stack_depth = 6MB<br /> max_fsm_pages = 204800<br/> wal_buffers = 921MB<br /> checkpoint_segments = 10<br /> checkpoint_completion_target = 0.9<br /> enable_seqscan= off<br /> random_page_cost = 4.0<br /> effective_cache_size = 1536MB<br /> log_destination = 'stderr'<br/> logging_collector = on<br /> log_directory = 'pg_log'<br /> log_truncate_on_rotation = on<br /> log_rotation_age= 1d<br /> log_rotation_size = 100MB<br /> syslog_facility = 'LOCAL0'<br /> syslog_ident = 'postgres'<br/> silent_mode = on<br /> log_duration = on<br /> log_line_prefix = '%t - user=%u,db=%d '<br /> log_statement= 'all'<br /> datestyle = 'iso, mdy'<br /> lc_messages = 'C'<br /> lc_monetary = 'C'<br /> lc_numeric = 'C'<br/> lc_time = 'C'<br /> default_text_search_config = 'pg_catalog.english'<br /><br /> The strange thing is that thismorning explain analyze now gives a much better duration : <br /> 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=3186430.11..3186466.21 rows=205 width=123) (actual time=3.075..3.325 rows=1 loops=1)<br /> -> Sort (cost=3186430.11..3186439.13 rows=3610 width=123) (actual time=3.073..3.176 rows=187 loops=1)<br /> Sort Key:constantestablenbienservice.code, constantestablenbienservice.id, constantestablenbienservice.lib_code<br /> Sort Method: quicksort Memory: 24kB<br /> -> Hash Left Join (cost=57.27..3186216.80 rows=3610 width=123)(actual time=0.913..2.795 rows=187 loops=1)<br /> Hash Cond: (constantestablebienservice.bs_code= constantestablenbienservice.id)<br /> Filter: (constantestablenbienservice.parentIS NULL)<br /> -> Nested Loop Left Join (cost=35.39..3186095.62 rows=7221width=4) (actual time=0.308..1.896 rows=187 loops=1)<br /> -> Nested Loop (cost=0.00..5315.38rows=1315 width=25) (actual time=0.164..0.250 rows=30 loops=1)<br /> -> Index Scan using t_demande_pkey on t_demande constantestabledemande (cost=0.00..8.32 rows=1 width=25) (actual time=0.107..0.108rows=1 loops=1)<br /> Index Cond: ((id_tech)::text = 'y+3eRapRQjW8mtL4wHd4/A=='::text)<br/> -> Index Scan using idx_operation_demande on t_operationconstantestableoperation (cost=0.00..5293.91 rows=1315 width=50) (actual time=0.053..0.107 rows=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 Scan on t_bien_serviceconstantestablebienservice (cost=35.39..2409.22 rows=770 width=29) (actual time=0.040..0.044 rows=6 loops=30)<br/> Recheck Cond: ((constantestablebienservice.id_operation)::text = (constantestableoperation.id_tech)::text)<br/> -> Bitmap Index Scan on idx_bien_service_operation (cost=0.00..35.19 rows=770 width=0) (actual time=0.037..0.037 rows=6 loops=30)<br /> Index Cond: ((constantestablebienservice.id_operation)::text = (constantestableoperation.id_tech)::text)<br/> -> Hash (cost=19.33..19.33 rows=205 width=127) (actualtime=0.566..0.566 rows=205 loops=1)<br /> -> Index Scan using n_bien_service_pkey on n_bien_serviceconstantestablenbienservice (cost=0.00..19.33 rows=205 width=127) (actual time=0.045..0.294 rows=205 loops=1)<br/> Total runtime: 3.518 ms<br /> (21 lignes)<br /><br /> There were no modification made on the database excepta restart yesterday evening and a vacuumdb --analyse ran at night.<br /><br /><br /><br /></font></font><div class="moz-signature"><tableborder="0" cellpadding="0" cellspacing="0"><tbody><tr><td colspan="3"><hr /></td></tr><tr><tdvalign="middle" width="50"><img align="middle" alt="DGFIP" border="0" height="48" moz-do-not-send="false"name="Image1" src="cid:part1.05090402.04010005@dgfip.finances.gouv.fr" width="41" /></td><td valign="top"width="184"><font color="#023854" face="Arial, sans-serif" size="1" style="font-size: 8pt;"> <b>Nicolas Jouanin</b></font><br /><font color="#c28f28" face="Arial, sans-serif" size="1" style="font-size: 8pt;"> <b>Analyste - TVA8ièmedirective</b> </font><br /><font color="#c28f28" face="Arial, sans-serif" size="1" style="font-size: 8pt;"> <b>BureauSI-1C / DAP2</b> </font><br /></td><td align="right" valign="top" width="124"><font color="#023854" face="Arial,sans-serif" size="1" style="font-size: 8pt;"> <b>Tel: 02.51.88.50.18</b> </font><br /></td></tr></tbody></table><br/><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td align="center" valign="middle"width="50"><img align="middle" alt="Eco-attitude" border="0" height="27" moz-do-not-send="false" src="cid:part2.02010002.07070406@dgfip.finances.gouv.fr"width="27" /></td><td valign="top" width="250"><font color="#36a629"face="Arial, sans-serif" size="1" style="font-size: 8pt;"> <b>Adoptez l'éco-attitude.</b> </font><br /><fontcolor="#36a629" face="Arial, sans-serif" size="1" style="font-size: 7pt;">N'imprimez ce courriel que si c'est vraimentnécessaire</font><br /></td></tr></tbody></table></div><br /><br /> -------- Message original --------<br /> Sujet :Re: [PERFORM] Two different execution plan for the same request<br /> De : Yeb Havinga <a class="moz-txt-link-rfc2396E"href="mailto:yebhavinga@gmail.com"><yebhavinga@gmail.com></a><br /> Pour : JOUANIN Nicolas(44) <a class="moz-txt-link-rfc2396E" href="mailto:nicolas.jouanin@dgfip.finances.gouv.fr"><nicolas.jouanin@dgfip.finances.gouv.fr></a><br/> Copie à : <aclass="moz-txt-link-abbreviated" href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a><br/> Date : 07/07/2010 10:27<br /><blockquotecite="mid:4C343A59.7050908@gmail.com" type="cite">JOUANIN Nicolas (44) wrote: <br /><blockquote type="cite">Hi,<br /><br /> I've trouble with some SQL request which have different execution plans when ran on two differentservers. One server is the development environment, the othe rone is th pre-production env. <br /> Both serversrun postgreSQL 8.3.0 on Linux and : <br /> - both databases contains the same data (pg_dump/pg_restore between servers)<br /> - instances have the same configuration parameters <br /> - vaccum and analyze is run every day. <br />The only difference I can see is the hardware. The pre-preoduction env. has more RAM, more CPU and a RAID5 disk array throughLVM where data are stored. <br /></blockquote> Hello Jouanin, <br /><br /> Could you give some more information followingthe guidelines from <a class="moz-txt-link-freetext" href="http://wiki.postgresql.org/wiki/SlowQueryQuestions">http://wiki.postgresql.org/wiki/SlowQueryQuestions</a>? <br /><br/> Essential are the contents from both conf files (comments may be removed). <br /><br /> regards, <br /> Yeb Havinga<br /><br /><br /><br /></blockquote><br />
В списке pgsql-performance по дате отправления: