pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: D. Dante Lorenso <dante@lorenso.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Indexing a Boolean or Null column?
Date: Sun, 04 Jan 2004 02:48:18 -0500
Message-ID: <19149.1073202498@sss.pgh.pa.us> (raw)
In-Reply-To: <3FF7BF29.8070401@familyhealth.com.au>
References: <3FF769EA.7090508@lorenso.com>
	<3FF79764.6060805@familyhealth.com.au>
	<3FF7A395.4050106@lorenso.com>
	<3FF7BF29.8070401@familyhealth.com.au>

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> Ok, so ...evenly distributed data on small set of values forces
>> sequential scan since that's faster.  I expected that based on
>> what I've read so far.

> Actually, it's more a case of that fetching an item via and index is 
> considered, say, four times slower than fetching something off a 
> sequential scan (sort of).  Hence, if you are selecting more than 25% of 
> the table, then a sequential scan will be faster, even though it has to 
> process more rows.

Actually it's worse than that: if an indexscan is going to fetch more
than a few percent of the table, the planner will think it slower than
a sequential scan --- and usually it'll be right.  The four-to-one ratio
refers to the cost of fetching a whole page (8K) randomly versus
sequentially.  In a seqscan, you can examine all the rows on a page
(dozens to hundreds usually) for the price of one page fetch.  In an
indexscan, one page fetch might bring in just one row that you care
about.  So the breakeven point is a lot worse than 4:1.

There is constant debate about the values of these parameters; in
particular the 4:1 page fetch cost ratio breaks down if you are able
to cache a significant fraction of the table in RAM.  See the list
archives for details.  But it's certainly true that an indexscan has to
be a lot more selective than 25% before it's going to be a win over
a seqscan.  I'd say 1% to 5% is the right ballpark.

			regards, tom lane



view thread (8+ messages)  latest in thread

Message-ID: <19149.1073202498@sss.pgh.pa.us>
Permalink:  ../19149.1073202498@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/19149.1073202498@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, chriskl@familyhealth.com.au, dante@lorenso.com
  Subject: Re: Indexing a Boolean or Null column?
  In-Reply-To: <19149.1073202498@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