Re: pg_stat_all_indexes understand

On Thu, Jul 9, 2015 at 09:45:25AM -0700, Jeff Janes wrote:
>
> 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.

And it will be documented in 9.5:

commit 7e9ed623d9988fcb1497a2a8ca7f676a5bfa136f
Author: Bruce Momjian <bruce(at)momjian(dot)us>
Date: Thu Mar 19 22:38:12 2015 -0400

docs: mention the optimizer can increase the index usage count

Report by Marko Tiikkaja

+ The optimizer also accesses indexes to check for supplied constants
+ whose values are outside the recorded range of the optimizer statistics
+ because the optimizer statistics might be stale.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +