> > 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.
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match