From: Bruce Momjian <bruce@momjian.us>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Nicolas Paris <niparisco@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: pg_stat_all_indexes understand
Date: Wed, 9 Sep 2015 17:51:33 -0400
Message-ID: <20150909215133.GE16463@momjian.us> (raw)
In-Reply-To: <CAMkU=1wvRL88u_X0DSc78_TTCji0HqdDkKovN1wKoxEQcrV4cQ@mail.gmail.com>
References: <CA+ssMOSO0Attq05RJmux8U0Sp3SQt9N=H2dDnbxCqzoMZ_jQCg@mail.gmail.com>
<CAMkU=1wvRL88u_X0DSc78_TTCji0HqdDkKovN1wKoxEQcrV4cQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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 <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. +
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bruce@momjian.us, jeff.janes@gmail.com, niparisco@gmail.com
Subject: Re: pg_stat_all_indexes understand
In-Reply-To: <20150909215133.GE16463@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox