Marko Tiikkaja <marko@joh.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.
> 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".
I think that's utter nonsense. Even if there were any validity to the
position, it wouldn't be enough to justify doubling the stats footprint
in order to track system-driven accesses separately from query-driven
ones.
regards, tom lane