Re: reducing random_page_cost from 4 to 2 to force index scan

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: Jesper Krogh <jesper(at)krogh(dot)cc>, pgsql-performance(at)postgresql(dot)org
Subject: Re: reducing random_page_cost from 4 to 2 to force index scan
Date: 2011-05-16 17:24:53
Message-ID: 18337.1305566693@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Sun, May 15, 2011 at 9:49 PM, Jesper Krogh <jesper(at)krogh(dot)cc> wrote:
>> Ok, it may not work as well with index'es, since having 1% in cache may very
>> well mean that 90% of all requested blocks are there.. for tables in should
>> be more trivial.

> Why would the index have a meaningful hot-spot unless the underlying
> table had one as well? (Of course the root block will be a hot-spot,
> but certainly not 90% of all requests)

The accesses to an index are far more likely to be clustered than the
accesses to the underlying table, because the index is organized in a
way that's application-meaningful and the table not so much. Continuing
the earlier example of a timestamp column, accesses might preferentially
hit near the right end of the index while the underlying rows are all
over the table.

IOW, hot spots measured at the row level and hot spots measured at the
page level could very easily be different between table and index.

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeff 2011-05-16 17:54:06 Re: Using pgiosim realistically
Previous Message John Rouillard 2011-05-16 17:06:36 Re: Using pgiosim realistically