Re: get_actual_variable_range vs idx_scan/idx_tup_fetch

On Fri, Oct 17, 2014 at 06:15:37PM -0400, Tom Lane wrote:
> Marko Tiikkaja <marko(at)joh(dot)to> writes:
> > On 10/17/14, 11:59 PM, Tom Lane wrote:
> >> Well, the index might've been getting used in queries too in a way that
> >> really only involved the first column. I think you're solving the wrong
> >> problem here. The right problem is how to identify indexes that are
> >> being used in a way that doesn't exploit all the columns.
>
> > I'm not sure I agree with that. Even if there was some information the
> > planner could have extracted out of the index by using all columns (thus
> > appearing "fully used" in these hypothetical new statistics), I still
> > would've wanted the index gone. But in this particular case, an index
> > on foo(a) alone was not selective enough and it would have been a bad
> > choice for practically every query, so I'm not sure what good those
> > statistics were in the first place.
>
> Those stats were perfectly valid: what the planner is looking for is
> accurate minimum and maximum values for the index's leading column, and
> that's what it got. You're correct that a narrower index could have given
> the same results with a smaller disk footprint, but the planner got the
> results it needed from the index you provided for it to work with.

Uh, why is the optimizer looking at the index on a,b,c and not just the
stats on column a, for example? I am missing something here.

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

+ Everyone has their own god. +