Re: get_actual_variable_range vs idx_scan/idx_tup_fetch

On Sat, Oct 18, 2014 at 04:29:41PM +0200, Marko Tiikkaja wrote:
> On 10/18/14, 5:16 AM, Tom Lane wrote:
> >BTW, on re-reading that code I notice that it will happily seize upon
> >the first suitable index ("first" in OID order), regardless of how many
> >lower-order columns that index has got. This doesn't make any difference
> >I think for get_actual_variable_range's own purposes, because it's only
> >expecting to touch the endmost index page regardless. However, in light
> >of Marko's complaint maybe we should teach it to check all the indexes
> >and prefer the matching one with fewest columns? It would only take a
> >couple extra lines of code, and probably not that many added cycles
> >considering we're going to do an index access of some sort. But I'm
> >not sure if it's worth any extra effort --- I think in his example
> >case, there wasn't any narrower index anyway.
>
> Perhaps accidentally this would have helped in my case, actually,
> since I could have created a new, smaller index CONCURRENTLY and
> then seen that the usage of the other index stopped increasing.
> With the "pick the smallest OID" behaviour that was not possible.
> Another idea had was some way to tell the optimizer not to use that
> particular index for stats lookups, but probably the use case for
> such a feature would be a bit narrow.

Well, if the index is there, why not use it? I thought the problem was
just that you had no visibility into how those statistics were being
accessed. Most people think EXPLAIN shows all accesses, but obviously
now it doesn't.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +