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

On Wed, Feb 8, 2012 at 1:58 PM, Kevin Traster
<ktraster(at)freshgrillfoods(dot)com> wrote:
> 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.

This is not a problem with dead rows, but the index is not really
satisfying your query and the database has to look through an
indeterminate amount of rows until the 'limit 15' is satisfied. Yeah,
backwards scans are slower, especially for disk bound scans but you
also have to consider how many filter misses your have. The smoking
gun is here:

"Index Scan Backward using changes_shareschange on changes
(cost=0.00..925150.26 rows=181997 width=98) (actual time=3.161..15.843
rows=15 loops=1)
Filter: ((activity = ANY ('{4,5}'::integer[])) AND (mfiled >= $1))"

When you see Filter: xyz, xyz is what each record has to be compared
against after the index pointed you to an area(s) in the heap. It's
pure luck going forwards or backwards that determines how many records
you have to look through to get 15 good ones as defined by satisfying
the filter. To prove that one way or the other you can convert your
where to a boolean returning (and bump the limit appropriately)
expression to see how many records get filtered out.

merlin