pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Jesper Krogh <jesper@krogh.cc>
Cc: pgsql-performance@postgresql.org
Subject: Re: reducing random_page_cost from 4 to 2 to force index scan
Date: Mon, 16 May 2011 13:24:53 -0400
Message-ID: <18337.1305566693@sss.pgh.pa.us> (raw)
In-Reply-To: <BANLkTimw2xWYQ_Hz4vtLXx-2nZRjRfoMMw@mail.gmail.com>
References: <BANLkTikZhYc+h1=YDJfXwZEZc1tN3_VwCg@mail.gmail.com>
	<4DB6F4BB020000250003CEB9@gw.wicourts.gov>
	<BANLkTindB68ogMg8zrR7GrYEV+mQQddj0A@mail.gmail.com>
	<BANLkTik6OaVkJGEjs5--vcynUE36mWbzbg@mail.gmail.com>
	<2823.1305314437@sss.pgh.pa.us>
	<BANLkTimTqXAy7_KqQz_OW1RvMbDd9ojfqg@mail.gmail.com>
	<4DCD90F5.2010501@agliodbs.com>
	<BANLkTikcVUeRfocJPe_W+jfi5k1+gcLnxg@mail.gmail.com>
	<4DD0195A.9040305@agliodbs.com>
	<BANLkTika=a95=Pm97Evi-Q7e_9vs4FnkCQ@mail.gmail.com>
	<4DD07855.8080907@postnewspapers.com.au>
	<4DD07B4E.1020503@2ndquadrant.com>
	<4DD0AB16.5060808@krogh.cc>
	<4DD0ACD0.1090801@krogh.cc>
	<BANLkTimw2xWYQ_Hz4vtLXx-2nZRjRfoMMw@mail.gmail.com>

Jeff Janes <jeff.janes@gmail.com> writes:
> On Sun, May 15, 2011 at 9:49 PM, Jesper Krogh <jesper@krogh.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



view thread (43+ messages)  latest in thread

Message-ID: <18337.1305566693@sss.pgh.pa.us>
Permalink:  ../18337.1305566693@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/18337.1305566693@sss.pgh.pa.us

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, jeff.janes@gmail.com, jesper@krogh.cc
  Subject: Re: reducing random_page_cost from 4 to 2 to force index scan
  In-Reply-To: <18337.1305566693@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox