Re: how to make this database / query faster

On Sun, Mar 16, 2008 at 9:47 AM, Martin Gainty <mgainty(at)hotmail(dot)com> 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.