Обсуждение: pg_stat_all_indexes understand

Поиск
Список
Период
Сортировка

pg_stat_all_indexes understand

От
Nicolas Paris
Дата:
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.

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

Is there a better way to check which index could be delete ?

Thanks by advance.

Re: pg_stat_all_indexes understand

От
Jeff Janes
Дата:

On Thu, Jul 9, 2015 at 5:20 AM, Nicolas Paris <niparisco@gmail.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

Re: pg_stat_all_indexes understand

От
Bruce Momjian
Дата:
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@gmail.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@momjian.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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +