Re: pg_stat_all_indexes understand

От: Bruce Momjian
Тема: Re: pg_stat_all_indexes understand
Дата: ,
(см: обсуждение, исходный текст)
Ответ на: Re: pg_stat_all_indexes understand  (Jeff Janes)
Список: 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  <>

  + Everyone has their own god. +

В списке pgsql-performance по дате сообщения:

От: Bruce Momjian
Сообщение: Re: pg_stat_all_indexes understand
От: Reza Taheri
Сообщение: Announcing the public availability of the TPCx-V prototype