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 1EE7ED1B454 for ; Sun, 4 Jan 2004 05:25:35 +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 40461-02 for ; Sun, 4 Jan 2004 01:24:45 -0400 (AST) Received: from sccrmhc12.comcast.net (sccrmhc12.comcast.net [204.127.202.56]) by svr1.postgresql.org (Postfix) with ESMTP id 6E349D1B479 for ; Sun, 4 Jan 2004 01:24:44 -0400 (AST) Received: from lorenso.com (c-24-1-26-144.client.comcast.net[24.1.26.144]) by comcast.net (sccrmhc12) with ESMTP id <2004010405244101200hrj9ce>; Sun, 4 Jan 2004 05:24:44 +0000 Message-ID: <3FF7A395.4050106@lorenso.com> Date: Sat, 03 Jan 2004 23:24:37 -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: Christopher Kings-Lynne Cc: pgsql-performance@postgresql.org Subject: Re: Indexing a Boolean or Null column? References: <3FF769EA.7090508@lorenso.com> <3FF79764.6060805@familyhealth.com.au> In-Reply-To: <3FF79764.6060805@familyhealth.com.au> 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/19 X-Sequence-Number: 5194 Christopher Kings-Lynne wrote: > > 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. > An index just on a boolean column won't be 'selective enough'. > eg. The index will only be able to choose 50% of the table - > since it's faster to do a full table scan in that case, the > index won't get used. 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. > A multi keyed index, however will work a bit better, eg an index > over (data_lookup_key, data_is_active). > > That way, the index will first be able to find the correct > key (which is nicely selective) and then will be able to > halve the resulting ? search space to get the active ones. I'm not using the 50% TRUE / 50% FALSE model. My model will be more like only ONE value IS TRUE for 'is_active' for each 'data_lookup_key' in my table. All the rest are FALSE. In this case for 100 rows all having the same 'data_lookup_key' we are looking at a 99% FALSE / 1% TRUE model ... and what I'll be searching for is the ONE TRUE. In this case, it WILL pay off to have the index on a boolean column, yes? Will I win my debate with my collegue? ;-) I think Tom Lanes suggestion of partial indexes is what I need to look into. > BTW, you shouldn't use 'banana', 'pear', etc as the data_lookup_key, > you should make another table like this: ... And then replace the > data_lookup_key col with a column of integers that is a foreign > key to the names table - waaaaay faster to process. Gotcha, yeah, I'm targeting as close to 3NF as I get get. Was just trying to be generic for my example ... bad example, oops. Dante