Re: how to make this database / query faster
| От | Richard Broersma |
|---|---|
| Тема | Re: how to make this database / query faster |
| Дата | |
| Msg-id | 396486430803161016p1aebf370o5449ad6e8bc6044c@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: how to make this database / query faster ("Martin Gainty" <mgainty@hotmail.com>) |
| Список | pgsql-general |
On Sun, Mar 16, 2008 at 9:47 AM, Martin Gainty <mgainty@hotmail.com> wrote:
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.
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.
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.
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.
В списке pgsql-general по дате отправления: