Re: pg_stat_all_indexes understand

From: Bruce Momjian
Subject: Re: pg_stat_all_indexes understand
Date: ,
Msg-id: 20150909215133.GE16463@momjian.us
(view: Whole thread, Raw)
In response to: Re: pg_stat_all_indexes understand  (Jeff Janes)
List: pgsql-performance

On Thu, Jul  9, 2015 at 09:45:25AM -0700, Jeff Janes wrote:
>
> On Thu, Jul 9, 2015 at 5:20 AM, Nicolas Paris <> 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 <>
    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  <>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +



pgsql-performance by date:

From: Bruce Momjian
Date:
Subject: Re: pg_stat_all_indexes understand
From: Reza Taheri
Date:
Subject: Announcing the public availability of the TPCx-V prototype