X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6AFF7D1B46C for ; Sun, 4 Jan 2004 07:49:49 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 47228-08 for ; Sun, 4 Jan 2004 03:49:00 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id C6122D1B4AE for ; Sun, 4 Jan 2004 03:48:24 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id i047mI19019150; Sun, 4 Jan 2004 02:48:18 -0500 (EST) To: Christopher Kings-Lynne Cc: "D. Dante Lorenso" , pgsql-performance@postgresql.org Subject: Re: Indexing a Boolean or Null column? 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> Comments: In-reply-to Christopher Kings-Lynne message dated "Sun, 04 Jan 2004 15:22:17 +0800" Date: Sun, 04 Jan 2004 02:48:18 -0500 Message-ID: <19149.1073202498@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/22 X-Sequence-Number: 5197 Christopher Kings-Lynne 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