Обсуждение: help interpreting pg_stat_user_index view values
Need some help interpreting the results of queries against the
pg_stat_user_index view.
Given the following four contrieved indexes and their scan, read and fetch
values in pg_stat_user_index view:
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?
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?
For idx3 do it's values mean it's column specificity is not specific enough
to be a relatively useful index?
I am assuming an index with values like idx4 could never exist, it is an
impossible result. Is that a correct assumption?
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
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
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
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
> >> 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