To: Kevin Traster <ktraster@freshgrillfoods.com>
cc: pgsql-performance@postgresql.org
Subject: Re: index scan forward vs backward = speed difference of 357X slower!
In-reply-to: 
 <CAC7CH7GB2Yi1VqQ9hy8Yex0avGvgLfvQkHabFGfmZa1WRU3X+A@mail.gmail.com>
References: 
 <CAC7CH7GB2Yi1VqQ9hy8Yex0avGvgLfvQkHabFGfmZa1WRU3X+A@mail.gmail.com>
Comments: In-reply-to Kevin Traster <ktraster@freshgrillfoods.com>
	message dated "Tue, 07 Feb 2012 08:49:05 -0800"
Date: Wed, 08 Feb 2012 14:31:08 -0500
Message-ID: <29203.1328729468@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Kevin Traster <ktraster@freshgrillfoods.com> writes:
> 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.

Apparently, there are some rows passing the filter condition that are
close to the end of the index, but none that are close to the start.
So it takes a lot longer to find the first 15 matches in one case than
the other.  You haven't shown us the index definition, but I gather from
the fact that the scan condition is just a Filter (not an Index Cond)
that the index itself doesn't offer any clue as to whether a given row
meets those conditions.  So this plan is going to be doing a lot of
random-access heap probes until it finds a match.

> Why and how do I fix it?

Probably, you need an index better suited to the query condition.
If you have one and the problem is that the planner's not choosing it,
then this is going to take more information to resolve.

			regards, tom lane