Re: How does the planner deal with multiple possible indexes?

On Wed, Jul 19, 2006 at 07:54:49PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > Indeed, if I find a case where there's a large enough number of rows it
> > will choose the smaller index. But I'm wondering if it would be better
> > to always favor the smaller index, since it would (presumably) be easier
> > to keep it in cache?
>
> AFAICS, in existing releases that should happen, because the cost
> estimate varies with the size of the index. And it does happen for me
> in simple tests. You did not provide the requested information to help
> us find out why it's not happening for you.
>
> (I'm a bit worried about whether CVS HEAD may have broken this behavior
> with the recent changes in the indexscan cost equations ... but unless
> you are working with HEAD that's not relevant.)

No, this is 8.1.3, and it's a production machine so I'd prefer not to go
about dropping indexes to get cost comparisons; unless there's some way
to disable the use of an index in a given backend? Otherwise I'll try
and come up with a test case.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461