| From: | Claudio Freire <klaussfreire(at)gmail(dot)com> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: Efficiently query for the most recent record for a given user |
| Date: | 2013-08-07 19:13:48 |
| Message-ID: | CAGTBQpb7qmmj-TZ+Q4AJ3ToEfTogJ6dRVYnGcRSfSbT4pEFbJw@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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).
| From | Date | Subject | |
|---|---|---|---|
| Next Message | slapo | 2013-08-08 06:59:42 | Re: [PERFORM] RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it. |
| Previous Message | Alvaro Herrera | 2013-08-07 19:05:48 | Re: Efficiently query for the most recent record for a given user |