From: Rural Hunter <ruralhunter@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: index scan forward vs backward = speed difference of 357X slower!
Date: Wed, 08 Feb 2012 16:01:44 +0800
Message-ID: <4F322BE8.6070701@gmail.com> (raw)
In-Reply-To: <CAC7CH7GB2Yi1VqQ9hy8Yex0avGvgLfvQkHabFGfmZa1WRU3X+A@mail.gmail.com>
References: <CAC7CH7GB2Yi1VqQ9hy8Yex0avGvgLfvQkHabFGfmZa1WRU3X+A@mail.gmail.com>
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
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 buffer
but the ones of desc order are not, while your disk IO is very slow?<br>
btw, your mem configuration of work_men is 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">
<p
style="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>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Dedicated DB server</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">4GB ram</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Shared_Buffers = 1 GB</font></p>
<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>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Work_mem = 32GB</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Analyze done</font></p>
<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>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Default Statistics = 1000</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif"><br>
</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Query (5366ms) :</font></p>
<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
analyze select 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) and mfiled
>= (select max(mfiled) from changes) order by
shareschange asc limit 15 </span></p>
<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></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px">Slow
Ascending explain Analyze:</p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><a
moz-do-not-send="true"
href="http://explain.depesz.com/s/zFz">http://explain.depesz.com/s/zFz</a></p;
<br class="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></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Query (15ms) :</font></p>
<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
analyze select 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) and mfiled
>= (select max(mfiled) from changes) order by
shareschange desc limit 15 </span></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><br>
</p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Fast descending explain analyze:</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><a
moz-do-not-send="true"
href="http://explain.depesz.com/s/OP7">http://explain.depesz.com/s/OP7</a></p;
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><br>
</p>
<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>
<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">The
index: </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><span
style="font-family:arial,sans-serif;font-size:13.2px">btree
index created with default ascending order</span></p>
<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>
<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">The
query plan and estimates are exactly the same, except desc
has index scan backwards instead of index scan for
changes_shareschange.</span></p>
<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>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Yet, actual runtime performance is
different by 357x slower for the ascending version instead
of descending.</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif"><br>
</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Why and how do I fix it?</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif"><br>
</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif"><br>
</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif"><br>
</font></p>
</span>
</blockquote>
<br>
</body>
</html>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ruralhunter@gmail.com
Subject: Re: index scan forward vs backward = speed difference of 357X slower!
In-Reply-To: <4F322BE8.6070701@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox