Re: index scan forward vs backward = speed difference of 357X slower!

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