Re: Why no performance boost although I added an index?

On Mon, 7 Apr 2003, Holger Marzen wrote:

> I have an accounting table on postgres 7.2.4, and my favourite select
> gets no performance boost if I add an index on the date column. But it
> should be faster.
>

Not necessarily (see below)

> So the index is used, but the execution time is greater than without
> index. Why that? Is the overhead using an index the biggest factor
> because there are only a few thousand rows in the table?

If the index scan is reading a large enough percentage of the rows (and
depending on the clustering of values), it may be reading enough pages
that there's no advantage (or even a disadvantage) to using the index.
This is due to both the reads of the index itself and the fact that it'll
often be reading the values in the main table (it still needs to get the
commit info from the table data) in random order rather than sequential
order which can lose some optimizations the OS often gives to sequential
reads.