Re: get_actual_variable_range vs idx_scan/idx_tup_fetch

On 10/17/14, 11:59 PM, Tom Lane wrote:
> Marko Tiikkaja <marko@joh.to> writes:
>> On 10/17/14, 11:47 PM, Tom Lane wrote:
>>> Marko Tiikkaja <marko@joh.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