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

Typo: Work_mem = 32 MB

The definition for both column and index:
shareschange | numeric |
"changes_shareschange" btree (shareschange)

Index created using: CREATE INDEX changes_shareschange ON changes(shareschange);

The entire table is created nightly (and analyzed afterwords), and
used only for reporting - there no updates/deletes, so there shouldn't
be any dead rows in the table.
Likewise, there is no nulls in the column.

Please elaborate on:

>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

Are you saying it is the retrieval of the physically random located 15
rows to meet the ascending condition that causes the 5 sec difference?
The table is not-clustered, so it is "random" for descending also.

The condition is shareschange ascending, I have an index for that
condition and the planner is using it.

What else can I look at?

On Wed, Feb 8, 2012 at 11:31 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Kevin Traster <ktraster(at)freshgrillfoods(dot)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