On 04/17/2011 03:52 AM, Phoenix Kiula wrote:
Hi,
Need some help here. I have a large table (200 million rows already).
There are two columns.
ColA
ColB
There is an index on ColA. It's an important column.
ColB is a boolean. Either 1 or 0.
For about 10% of the data, ColB is 1. Otherwise it's default is 0.
Now, my question: for that 10%, is it worth adding a conditional index
on "ColA WHERE ColB = 1"? Will this save time for the queries that are
related to 10% of my data where the ColB is 1? Or will the main
leading index on ColA already speed things up as much as it can?
Thanks for any ideas!
PK
I'll take a wild guess:
it depends on your data:
if colA is highly selectable (ie unique or very nearly so), then it wont help.
if colA is much less unique, and adding colB = 1 will drop the dup's a lot, and you'll
always frame query query as "ColA = ?? and colB = 1", then it'll help.
said another way, if index on colA gets you very close to what you need, then
index on colB wont help. If index on colA still has a lot of similar results,
then you gotta ask yourself: will very few have colB = 1? If yes, then add the
index.
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
