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 по дате отправления:

Предыдущее
От: Yeb Havinga
Дата:
Сообщение: Re: Two different execution plan for the same request
Следующее
От: Yeb Havinga
Дата:
Сообщение: Re: Two different execution plan for the same request