help interpreting pg_stat_user_index view values

On Fri, Jan 3, 2014 at 10:53 PM, <dennisr@visi.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.ru@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 06/01/14 11:08, Sergey Konoplev wrote:
[...]

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.

[...]

An index only used by an annual report, should possibly be only created
prior to the report run & dropped immediately afterwards - why carry its
overhead for the bulk of the year?

Cheers,
Gavin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Jan 5, 2014 at 2:19 PM, Gavin Flower
<GavinFlower@archidevsys.co.nz> wrote:

On 06/01/14 11:08, Sergey Konoplev wrote:
[...]

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.

[...]

An index only used by an annual report, should possibly be only created
prior to the report run & dropped immediately afterwards - why carry its
overhead for the bulk of the year?

I fully agree. This is the matter of implementation.

--
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.ru@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

You could also look into a filtered index that perhaps only covers dates
earlier than a certain point in time where regular performance wouldn't be
hindered. But Gavin is absolutely right otherwise.

On Jan 5, 2014 5:22 PM, "Sergey Konoplev" <gray.ru@gmail.com> wrote:

On Sun, Jan 5, 2014 at 2:19 PM, Gavin Flower
<GavinFlower@archidevsys.co.nz> wrote:

On 06/01/14 11:08, Sergey Konoplev wrote:
[...]

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