Quick Links

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

From:	Rural Hunter <ruralhunter(at)gmail(dot)com>
To:	pgsql-performance(at)postgresql(dot)org
Subject:	Re: index scan forward vs backward = speed difference of 357X slower!
Date:	2012-02-08 08:01:44
Message-ID:	4F322BE8.6070701@gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-performance

<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? 
btw, your mem configuration of work_men is very strange. 
 
于 2012/2/8 0:49, Kevin Traster 写道:
<blockquote
cite="mid:CAC7CH7GB2Yi1VqQ9hy8Yex0avGvgLfvQkHabFGfmZa1WRU3X+A(at)mail(dot)gmail(dot)com"
type="cite">
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
Dedicated DB server
4GB ram
Shared_Buffers = 1 GB
Effective_cache_size = 3GB
Work_mem = 32GB
Analyze done
Queries ran multiple times, same
differences/results
Default Statistics = 1000
 

Query (5366ms) :
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 
 


Slow
Ascending explain Analyze:
<a
moz-do-not-send="true"
href="http://explain.depesz.com/s/zFz">http://explain.depesz.com/s/zFz</a>
 
 

Query (15ms) :
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 
 

Fast descending explain analyze:
<a
moz-do-not-send="true"
href="http://explain.depesz.com/s/OP7">http://explain.depesz.com/s/OP7</a>
 

 

The
index: changes_shareschange
is
a btree
index created with default ascending order
 

The
query plan and estimates are exactly the same, except desc
has index scan backwards instead of index scan for
changes_shareschange.
 

Yet, actual runtime performance is
different by 357x slower for the ascending version instead
of descending.
 

Why and how do I fix it?
 

 

 


</blockquote>
 
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	7.8 KB

In response to

index scan forward vs backward = speed difference of 357X slower! at 2012-02-07 16:49:05 from Kevin Traster

Browse pgsql-performance by date

	From	Date	Subject
Next Message	Rural Hunter	2012-02-08 14:36:30	Re: index scan forward vs backward = speed difference of 357X slower!
Previous Message	Greg Smith	2012-02-08 03:13:24	Re: how to demonstrate the effect of direct I/O ?