pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_stat_all_indexes understand
3+ messages / 3 participants
[nested] [flat]
* pg_stat_all_indexes understand
@ 2015-07-09 12:20 Nicolas Paris <niparisco@gmail.com>
2015-07-09 16:45 ` Re: pg_stat_all_indexes understand Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Nicolas Paris @ 2015-07-09 12:20 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_stat_all_indexes understand
2015-07-09 12:20 pg_stat_all_indexes understand Nicolas Paris <niparisco@gmail.com>
@ 2015-07-09 16:45 ` Jeff Janes <jeff.janes@gmail.com>
2015-09-09 21:51 ` Re: pg_stat_all_indexes understand Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 3+ messages in thread
From: Jeff Janes @ 2015-07-09 16:45 UTC (permalink / raw)
To: Nicolas Paris <niparisco@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_stat_all_indexes understand
2015-07-09 12:20 pg_stat_all_indexes understand Nicolas Paris <niparisco@gmail.com>
2015-07-09 16:45 ` Re: pg_stat_all_indexes understand Jeff Janes <jeff.janes@gmail.com>
@ 2015-09-09 21:51 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 3+ messages in thread
From: Bruce Momjian @ 2015-09-09 21:51 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Nicolas Paris <niparisco@gmail.com>; 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 <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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2015-09-09 21:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-07-09 12:20 pg_stat_all_indexes understand Nicolas Paris <niparisco@gmail.com>
2015-07-09 16:45 ` Jeff Janes <jeff.janes@gmail.com>
2015-09-09 21:51 ` Bruce Momjian <bruce@momjian.us>
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