Re: Unused expression indexes

Maciek Sakrejda <maciek(at)pganalyze(dot)com> writes:
> Thanks, but I was asking specifically about _unused_ indexes
> (according to pg_stat_user_indexes). Bruce's blog post showed how they
> can still influence rowcount estimates, but can they do that (1) even
> if they don't end up being used by the query plan and (2) in a way
> that leads to a different plan?

Certainly. This example was too simple to illustrate that point
perhaps, but we would have arrived at the same rowcount estimate
whether it then chose to use the index or not. (You could prove
that with the same example by modifying the comparison constant
to make the rowcount estimate high enough to discourage use of
the index.) In turn, a different rowcount estimate might change
the plan for subsequent joins or other processing. I didn't
spend enough time on the example to show that, but it's surely
not hard to show.

regards, tom lane