Re: Indices types, what to use. Btree, Hash, Gin or Gist

Mohamed <mohamed5432154321(at)gmail(dot)com> writes:

> My Gmail(bloody gmail!) has been auto-replying to the last messager (Scott)
> so I think we have been having a private discussion on this topic.

There is an option in the Google Labs tab to make "Reply All" the default
button -- of course then there's always a chance you'll make the opposite
mistake which can be a lot worse.

Earlier I suggested with a boolean column you could consider making it the
condition on a partial index with some other key. For example you could have

CREATE INDEX partial_age_male on tab(age) WHERE gender = 'M';
CREATE INDEX partial_age_female on tab(age) WHERE gender = 'F';

Then if you always search on age with gender the optimizer can use the index
which only includes the records for the appropriate gender. It's basically a
"free" index key column since it doesn't actually have to store the extra
column.

Note that in this example if you were to search on just age it wouldn't be
able to use either of these indexes however. In theory it could use the
indexes if you search on just gender but it would be unlikely to for all the
same reasons as previously mentioned for regular indexes.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!