On Sun, Mar 16, 2008 at 9:47 AM, Martin Gainty <
mgainty@hotmail.com> wrote:
My understanding is that Partial index is implemented for low cardinality scenarios ('Y'/'N') ('T'/'F') (null/not null) ?
Low cardinality can apply for more than just boolean or null/not null?
Let say I wanted to run the majority of my select queries on users with the name 'Richard'. Now of the billion users that I have, only 500 have the name 'Richard'. Since 'Richard' only makes up an insignificant part of the users table, have a partial index on 'Richard' would greatly improve select query performance for these kinds of queries.
If your boolean fields T and F were about 50% even throughout your entire trillion record table, a partial index wouldn't do much to help since 50% isn't selective enough. The same thing applies for records that have an even distribution of nulls and not nulls.
Would it matter the selectivity is balanced?
thus 1 null record
In this case, a partial index would be a really good idea if you were mostly interested in records that *were* null. However, if you were most interested in records that were not null in a table distribution like this, then a partial index would not do much for you in this case.
and 1 trillion null records would not apply
once again, assuming that you are mostly interested in querying the NOT NULL records in a mostly null record table, then a partial index would be a really good idea for query speed improvement.
I hope I am making sense.
Regards,
Richard Broersma Jr.