Re: get_actual_variable_range vs idx_scan/idx_tup_fetch

On 10/17/14, 11:59 PM, Tom Lane wrote:
> Marko Tiikkaja <marko(at)joh(dot)to> writes:
>> On 10/17/14, 11:47 PM, Tom Lane wrote:
>>> Marko Tiikkaja <marko(at)joh(dot)to> writes:
>>>> So what I'd like to have is a way to be able to distinguish between
>>>> indexes being used to answer queries, and ones being only used for stats
>>>> lookups during planning.
>
>>> Why? Used is used.
>
>> Because I don't need a 30GB index on foo(a,b,c) to look up statistics.
>> If I ever have a problem, I can replace it with a 5GB one on foo(a).
>
> 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.

I think there's a big difference between "this index was used to look up
stuff for planning" and "this index was used to answer queries quickly".
In my mind the first one belongs to the category "this index was
considered", and the latter is "this index was actually useful". But
maybe I'm not seeing the big picture here.

.marko