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 79172D1B442 for ; Sun, 4 Jan 2004 01:19:22 +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 09025-05 for ; Sat, 3 Jan 2004 21:18:39 -0400 (AST) Received: from sccrmhc13.comcast.net (sccrmhc13.comcast.net [204.127.202.64]) by svr1.postgresql.org (Postfix) with ESMTP id 41A69D1B454 for ; Sat, 3 Jan 2004 21:18:27 -0400 (AST) Received: from lorenso.com (c-24-1-26-144.client.comcast.net[24.1.26.144]) by comcast.net (sccrmhc13) with ESMTP id <20040104011835016008tgk8e>; Sun, 4 Jan 2004 01:18:35 +0000 Message-ID: <3FF769EA.7090508@lorenso.com> Date: Sat, 03 Jan 2004 19:18:34 -0600 From: "D. Dante Lorenso" User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Indexing a Boolean or Null column? Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/16 X-Sequence-Number: 5191 I've been debating with a collegue who argues that indexing a boolean column is a BAD idea and that is will actually slow down queries. My plan is to have a table with many rows sharing 'versions' (version/archive/history) of data where the most current row is the one where 'is_active' contains a true value. If the table begins to look like this: data_id(pk) | data_lookup_key | data_is_active | ... ------------+-----------------+----------------+-------- 1 | banana | false | ... 2 | banana | false | ... 3 | banana | false | ... 4 | banana | false | ... 5 | banana | false | ... 6 | banana | false | ... 7 | banana | false | ... 8 | banana | false | ... 9 | banana | true | ... 10 | apple | true | ... 11 | pear | false | ... 12 | pear | false | ... 13 | pear | false | ... 14 | pear | false | ... 15 | pear | false | ... ... 1000000 | pear | true | ... Will an index on the 'data_is_active' column be used or work as I expect? I'm assuming that I may have a million entries sharing the same 'data_lookup_key' and I'll be using that to search for the active version of the row. SELECT * FROM table WHERE data_lookup_key = 'pear' AND data_is_active IS TRUE; Does it make sense to have an index on data_is_active? Now, I've read that in some databases the index on a column that has relatively even distribution of values over a small set of values will not be efficient. I bet this is in a FAQ somewhere. Can you point me in the right direction? Dante