Search Postgresql Archives

Re: Indexing strategy

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux