Re: Index for low selectivity field

On 2/15/2012 8:16 AM, Robert James wrote:
> A table has a column "obj_type" which has very low selectivity (let's
> say 5 choices, with the top choice making up 50% of records). Is
> there any sense in indexing that column? B-trees won't be that useful,
> and the docs discourage other index types/
>

It, of course, depends on your usage.

Lets say you have lots and lots of records. And lets say you don't have
another field that can be used as a better index. And, lets say you are
interested in obj_type's not in the 50%. Then an index on obj_type
would be useful.

select * from table where obj_type = '10%_type'

would use the index to cut the table down to 10% and then do a table
scan on just that.

On the other hand, lets say you have a field that has better
selectivity. PG will ignore an index on obj_type because it can scan a
much smaller set by using the more selective index.

On the other hand, lets say you have one or two obj_type's you are
interested in, with a low % of records. Its possible to create a
functional index where obj_type in('a', 'b'). Then when you:

select ... where obj_type = 'a'

the index can be used, and it'll be more selective, and it'll be smaller.

On the other hand, lets say you dont have very many records.. and most
of them fit into ram. In that case an index wont really be useful
because PG can table scan very very fast.

-Andy