Re: Efficiently query for the most recent record for a given user

On Thu, Aug 8, 2013 at 5:01 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
>> On Wed, Aug 7, 2013 at 4:04 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> Yeah, but it's faster if it's in the same direction, because the
>>>> kernel read-ahead code detects sequential reads, whereas it doesn't
>>>> when it goes backwards. The difference can be up to a factor of 10 for
>>>> long index scans.
>>>
>>> Color me skeptical. Index searches are seldom purely sequential block
>>> accesses. Maybe if you had a freshly built index that'd never yet
>>> suffered any inserts/updates, but in practice any advantage would
>>> disappear very quickly after a few index page splits.
>>
>> Maybe.
>>
>> I've tested on pgbench test databases, which I'm not sure whether
>> they're freshly built indexes or incrementally built ones, and it
>> applies there (in fact backward index-only scans was one of the
>> workloads the read-ahead patch improved the most).
>
> It's been a while, but when I was touching the btree code for the
> SSI implementation I thought I saw something about a reverse scan
> needing to visit the parent page in cases where a forward scan
> doesn't, due to the locking techniques used in btree. I don't know
> how significant those extra trips up and down the tree are, but
> they must cost *something*.

From my benchmarks at the time (with pgbench), they seldom ever
happen, so even if they cost a lot, they don't add up to much.