Re: index scan forward vs backward = speed difference of 357X slower! - Mailing list pgsql-performance
From | Rural Hunter |
---|---|
Subject | Re: index scan forward vs backward = speed difference of 357X slower! |
Date | |
Msg-id | 4F322BE8.6070701@gmail.com Whole thread Raw |
In response to | index scan forward vs backward = speed difference of 357X slower! (Kevin Traster <ktraster@freshgrillfoods.com>) |
List | pgsql-performance |
what's the size of the index? is it too big to fit in shared_buffers? maybe the firt 15 rows by asc order are in bufferbut the ones of desc order are not, while your disk IO is very slow?<br /> btw, your mem configuration of work_menis very strange. <br /><br /> 于 2012/2/8 0:49, Kevin Traster 写道: <blockquote cite="mid:CAC7CH7GB2Yi1VqQ9hy8Yex0avGvgLfvQkHabFGfmZa1WRU3X+A@mail.gmail.com"type="cite"><span style="border-collapse:collapse"><pstyle="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="arial,sans-serif">PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51),64-bit</font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="arial, sans-serif">DedicatedDB server</font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="arial,sans-serif">4GB ram</font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><fontface="arial, sans-serif">Shared_Buffers =1 GB</font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="arial, sans-serif">Effective_cache_size= 3GB</font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><fontface="arial, sans-serif">Work_mem = 32GB</font><pstyle="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="arial, sans-serif">Analyzedone</font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="arial,sans-serif">Queries ran multiple times, same differences/results</font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><fontface="arial, sans-serif">Default Statistics= 1000</font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="arial, sans-serif"><br/></font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="arial, sans-serif">Query(5366ms) :</font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span style="color:rgb(11,55,77);font-family:inherit;background-color:rgb(255,255,255);font-size:12px;text-align:left">explain analyzeselect initcap (fullname), initcap(issuer),upper(rsymbol), initcap(industry), activity,to_char(shareschange,'FM9,999,999,999,999,999'),sharespchange||+ E'\%' from changes where activity in (4,5) andmfiled >= (select max(mfiled) from changes) order by shareschange asc limit 15 </span><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span style="color:rgb(11,55,77);font-family:inherit;background-color:rgb(255,255,255);font-size:12px;text-align:left"><br /></span><pstyle="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px">SlowAscending explain Analyze:<p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><ahref="http://explain.depesz.com/s/zFz" moz-do-not-send="true">http://explain.depesz.com/s/zFz</a><brclass="Apple-interchange-newline" /><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span style="color:rgb(11,55,77);font-family:inherit;background-color:rgb(255,255,255);font-size:12px;text-align:left"><br /></span><pstyle="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="arial, sans-serif">Query(15ms) :</font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span style="color:rgb(11,55,77);font-family:inherit;background-color:rgb(255,255,255);font-size:12px;text-align:left">explain analyzeselect initcap (fullname), initcap(issuer),upper(rsymbol), initcap(industry), activity,to_char(shareschange,'FM9,999,999,999,999,999'),sharespchange||+ E'\%' from changes where activity in (4,5) andmfiled >= (select max(mfiled) from changes) order by shareschange desc limit 15 </span><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><br/><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><fontface="arial, sans-serif">Fast descending explainanalyze:</font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><a href="http://explain.depesz.com/s/OP7"moz-do-not-send="true">http://explain.depesz.com/s/OP7</a><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><br/><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span style="font-family:arial,sans-serif;font-size:13.2px"><br/></span><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span style="font-family:arial,sans-serif;font-size:13.2px">Theindex: </span><span style="font-family:arial,sans-serif;font-size:13px">changes_shareschange</span><span style="background-color:rgb(255,255,255);color:rgb(11,55,77);font-family:inherit;font-size:12px;text-align:left">is a </span><spanstyle="font-family:arial,sans-serif;font-size:13.2px">btree index created with default ascending order</span><pstyle="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span style="font-family:arial,sans-serif;font-size:13.2px"><br/></span><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span style="font-family:arial,sans-serif;font-size:13.2px">Thequery plan and estimates are exactly the same, except desc has indexscan backwards instead of index scan for changes_shareschange.</span><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span style="font-family:arial,sans-serif;font-size:13.2px"><br/></span><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><fontface="arial, sans-serif">Yet, actual runtime performance isdifferent by 357x slower for the ascending version instead of descending.</font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><fontface="arial, sans-serif"><br /></font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><fontface="arial, sans-serif">Why and how do Ifix it?</font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="arial, sans-serif"><br/></font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="arial, sans-serif"><br/></font><p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="arial, sans-serif"><br/></font></span></blockquote><br />
pgsql-performance by date: