Thread: How reliable are the stats collector stats?

We've been running the stats collector for about a week now on a
heavily used production database (with all the various row/block level
gathering options enabled too).  This database, in terms of number of
tables and indexes is rather large.  About 100 tables, and many many
more indexes.

What we've found is that pg_stats_user_indexes shows a number indexes
that appear to be used at all (ie, zero's for idx_scan, x_tup_read, and
idx_tup_fetch columns).

Could pg_stats_user_indexes be lying?  Could I be misinterpreting the
data?

I realize the real question is "why aren't these indexes being used",
but that's something I need to figure out for myself.

thanks!

eric

Eric Ridge <ebr@tcdi.com> writes:
> Could pg_stats_user_indexes be lying?

Jan probably knows this stuff better than I, but my guess is that if the
counter type you are looking at is incrementing at all, then it's not
too far off.  I certainly can't think of a failure mechanism that would
cause some indexes to be shown with zero hits when other indexes do
get hits.

> I realize the real question is "why aren't these indexes being used",

Up to a point.  If it's a unique index then you may want the
uniqueness-check functionality even if the index is never used for
searches.  (I think that pg_stats only counts search probes, not
accesses made in connection with insertions, but I'm too tired to
go double-check this.)

            regards, tom lane

On Mar 13, 2004, at 12:51 AM, Tom Lane wrote:

> Eric Ridge <ebr@tcdi.com> writes:
>> Could pg_stats_user_indexes be lying?
>
> Jan probably knows this stuff better than I, but my guess is that if
> the
> counter type you are looking at is incrementing at all, then it's not
> too far off.

Many of the indexes that report zero usage I agree with.  Only a few
seem questionable.  Double-checking the queries (and their plans) will
provide the only true answer.

> I certainly can't think of a failure mechanism that would
> cause some indexes to be shown with zero hits when other indexes do
> get hits.

This is good to know.  I don't have specifics handy, but I've seen a
few columns from the pg_statio_user_tables view come back w/ null
values.  Oh yeah, this is against v7.3.4.

>> I realize the real question is "why aren't these indexes being used",
>
> Up to a point.  If it's a unique index then you may want the
> uniqueness-check functionality even if the index is never used for
> searches.

Very good point.  Fortunately the indexes in question are not unique
indexes.

thanks!

eric

On Mar 13, 2004, at 1:27 AM, Eric B.Ridge wrote:

> This is good to know.  I don't have specifics handy, but I've seen a
> few columns from the pg_statio_user_tables view come back w/ null
> values.  Oh yeah, this is against v7.3.4.

It's the various toast_ and idx_ columns that sometimes appear null.
And they're null only for those tables that haven't toasted anything
and/or don't have indexes.  This looks like normal behavior.

eric

Tom Lane wrote:

> Eric Ridge <ebr@tcdi.com> writes:
>> Could pg_stats_user_indexes be lying?
>
> Jan probably knows this stuff better than I, but my guess is that if the
> counter type you are looking at is incrementing at all, then it's not
> too far off.  I certainly can't think of a failure mechanism that would
> cause some indexes to be shown with zero hits when other indexes do
> get hits.

As described before in various threads, the messages from the backend to
the stats collector are unreliable INET UDP on purpose, so that a
clogged collector never slows down a backend.

If that happens, usually an entire bunch of not necessarily related
counter increments on a per transaction base would get lost.

>
>> I realize the real question is "why aren't these indexes being used",
>
> Up to a point.  If it's a unique index then you may want the
> uniqueness-check functionality even if the index is never used for
> searches.  (I think that pg_stats only counts search probes, not
> accesses made in connection with insertions, but I'm too tired to
> go double-check this.)

That is right. Only scans are counted for. A not scanned non-unique
index is obsolete or indicates a planner/casting problem.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #