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

On Sun, Feb 01, 2009 at 06:00:02PM +0100, Mohamed wrote:
> When it comes to the boolean, the content is about 70-30%. I find it strange
> though that an index on a 50-50% isn't that useful. With an index the DB can
> skip 50% of the table so it should be useful, but perhaps the intersection
> of sets is expensive for the DB?
> Could an index in fact possibly slow down queries? Or will the DB ignore
> using the index in such cases?

It's more complex than you suggest: the database cannot just skip 50%
of the table. The database reads or write blocks of data (8k) and each
such block will contain (in your example) 50% rows you are interested
in. So the database will have to read every block in the table anyway,
so you may as well not use the index at all.

Yes, the database will avoid using indexes if it decides they're a bad
idea.

Usually an index has to cut the number of blocks required by at least
90% before it becomes at all useful to use it. Indexes on booleans
rarely reach that kind of level.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.