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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Aug 8, 2013 at 5:01 PM, Kevin Grittner <kgrittn@xxxxxxxxx> wrote:
> Claudio Freire <klaussfreire@xxxxxxxxx> wrote:
>> On Wed, Aug 7, 2013 at 4:04 PM, Tom Lane <tgl@xxxxxxxxxxxxx> 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.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux