On Sun, Mar 16, 2008 at 9:47 AM, Martin Gainty <[EMAIL PROTECTED]> wrote:
> My understanding is that Partial index is implemented for low cardinality
> scenarios ('Y'/'N') ('T'/'F') (null/not null) ?
> http://en.wikipedia.org/wiki/Partial_index
>
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.
