Re: Indexing a Boolean or Null column?

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