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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
