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

On Wed, Jul 19, 2006 at 07:00:40PM -0400, Tom Lane wrote:
> "Gregory Maxwell" <gmaxwell(at)gmail(dot)com> writes:
> > On 7/19/06, Jim C. Nasby <jnasby(at)pervasive(dot)com> wrote:
> > [snip]
> >> \d does list bdata__ident_filed_departure before bdata_ident; I'm
> >> wondering if the planner is finding the first index with ident_id in it
> >> and stopping there?
>
> >> From my own experience it was grabbing the first that has the
> > requested field as its first member.. I haven't looked at the code to
> > see if that is the intended behavior.
>
> Ordering would only matter if the estimated costs were exactly the same,
> which they probably shouldn't be for indexes with such different sizes.
> However, if the estimated number of matching rows were real small, you
> might be winding up with a "one page to fetch" estimate in either case.
> Jim didn't provide enough details to guess what the cost estimates
> actually are...

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?
--
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