Re: pg_stat_all_indexes understand

On Thu, Jul 9, 2015 at 5:20 AM, Nicolas Paris <niparisco(at)gmail(dot)com> wrote:

> Hello,
>
> I wonder how understanding pg_stat_all_indexes working
>
> When I run an explain, some index are not used, but
> pg_stat_all_indexes.idx_scan is incremented for those indexes.
>

When the planner considers using a merge join on a indexed column, it uses
an index to check the endpoints of the column (the min and the max) to make
sure it has the latest values to get the most accurate estimate. This
causes the usage counts to get incremented. Even when it doesn't end up
using the merge join.

> Does this mean idx_scan is incremented each time the planner check if an
> index could be use whenever it won't use it ?
>

Not in general, only in a few peculiar cases.

Cheers,

Jeff