Re: insane index scan times

On Sun, Jun 03, 2007 at 11:29:07PM -0700, Sergei Shelukhin wrote:
> I wonder what exactly makes index perform 100+ times slower than
> seqscan - I mean even if it's perfromed on the HD which it should not
> be given the index size, index and table are on the same HD and index
> is smaller and also sorted, isn't it?

Um, because if you scan the whole index you also have to scan the whole
table, and you're going to scan the table and the index in random
order, which is slower again.

An index is faster for selecting a *portion* of the table, it's
useless once you get to a significant percentage.

However, recent versions have Bitmap index scans which are a middle
ground, linear index scan, linear heap scan which has a much better
worst case. So perhaps you're running a really old version of postgres,
you didn't actually say what version you were running.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.