Re: help interpreting pg_stat_user_index view values

On Fri, Jan 3, 2014 at 10:53 PM, <dennisr(at)visi(dot)com> wrote:
> Index name idx_scan idx_tup_read idx_tup_fetch
> idx1 100 0
> 0
> idx2 100 200 0
> idx3 100 200 50
> idx4 100 0
> 200
>
> Is idx1 a "useless" index? Is it being scanned but nevering returns useful
> tuples because it doesn't point to any useful rows in the table? Or maybe
> the query planner looked at the index but decided to use a table scan
> instead?

No, it just tells us that no tuples matched the index conditions for a
statistics collecting period on queries where planner chose this
index. Probably in the future there will be such tuples.

An index might be considered as useless when there were no idx scans
for the significantly long period. However it might be non-trivial to
define this period. Eg. one have a query building an annual report
that uses this index and the period here is one year.

> Is idx2 a "useless" index? Is this index being scanned but nevering returns
> useful tuples because it doesn't point to any useful rows in the table?

No, it is not. It tells us that there might be another statements in
the queries that prevent the read rows from fetching.

> For idx3 do it's values mean it's column specificity is not specific enough
> to be a relatively useful index?

No. The reason is the same as in the previous question. Eg OFFSET 150 LIMIT 50.

> I am assuming an index with values like idx4 could never exist, it is an
> impossible result. Is that a correct assumption?

Yes, this is correct one.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com