Skip site navigation (1) Skip section navigation (2)

Re: Indexing a Boolean or Null column?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
Cc: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Indexing a Boolean or Null column?
Date: 2004-01-04 07:48:18
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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

In response to

pgsql-performance by date

Next:From: Christopher BrowneDate: 2004-01-04 21:39:37
Subject: Re: Indexing a Boolean or Null column?
Previous:From: Christopher Kings-LynneDate: 2004-01-04 07:22:17
Subject: Re: Indexing a Boolean or Null column?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group