Re: index does not improve performance

On Thu, 2002-01-31 at 14:25, Milos Prudek wrote:
> It looks like creating an index degrades performance if the result set
> is similar to the size of the whole table (I had much better results
> when the condition was met by only two thousand records). Is this
> normal?

Yes -- for very large result sets, you will get worse performance using
an index scan than not using one.

I *believe* in 7.2 that Pg keeps good enough statistics to figure this
out most of the time (so it would use the index for the first query but
not the second); however, I may be wrong.

BTW, have you VACUUM ANALYZE'd this table? What version of Pg are you
running? The output for 'explain' for these queries (with and without
index) would be helpful.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC