Subject: monitoring-stats.html is too impenetrable

From: PG Doc comments form <[email-masked]>
Date: Sun, 24 Nov 2019 09:31:58 +0000

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/9.6/monitoring-stats.html
Description:

There needs to be a tutorial page explaining how to use pg_stat_all_tables
to find missing indexes, or maybe just an example on monitoring-stats.html
which is hopelessly inaccessible to a non-expert. I would have never been
able to figure out anything close to the solution at
https://www.reddit.com/r/PostgreSQL/comments/e0rx8l/i_was_missing_a_single_index_and_omgf_everything/
from the existing docs. Thank you for your kind consideration of this
request; please do not hesitate to send instructions for how to submit a
pull request for this, as I would gladly do so. Best regards, -Jim

---

From: Michael Paquier <[email-masked]>
Date: Mon, 25 Nov 2019 10:20:36 +0900

On Sun, Nov 24, 2019 at 09:31:58AM +0000, PG Doc comments form wrote:
> from the existing docs.

Well, it may be as simple as that in some cases, but you also need to
consider other parameters in more complex cases, like:
- Actual CPU consumption done by backends.
- Get stats about predicates (WHERE and JOIN clauses).
- Physical disk access.
- Anything else I don't have on top of my mind.

> Thank you for your kind consideration of this

The Postgres mailing lists are old-school regarding that, so pull
requests sent to the git repository on github or such are not
accepted.  Sending an email with a patch would be just but fine, and
here you would need to patch some of the *.sgml files in doc/.  So if
you have anything you'd like to get changed with fresh ideas, let's
see how you would like things to change and then let's discuss about
it.

Thanks,
--
Michael

---

From: James Salsman <[email-masked]>
Date: Wed, 4 Dec 2019 03:29:55 -0800

Michael,

Thank you for your thoughtful reply. This might be much easier:

How about adding another example to
https://www.postgresql.org/docs/11/planner-stats.html ?

SELECT relname, seq_scan-idx_scan AS too_much_seq,
   case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END,
   pg_relation_size(relid::regclass) AS rel_size, seq_scan, idx_scan
FROM pg_stat_all_tables
WHERE schemaname='public' AND pg_relation_size(relid::regclass)>80000
ORDER BY too_much_seq DESC;

The rationale and ideas for how to introduce the example at
https://www.reddit.com/r/PostgreSQL/comments/e0rx8l/i_was_missing_a_single_index_and_omgf_everything/

---

From: Michael Paquier <[email-masked]>
Date: Fri, 6 Dec 2019 12:04:45 +0900

> https://www.postgresql.org/docs/11/planner-stats.html ?

Not sure I see the parallel here.  This page talks about planner
statistics, and yours about being able to find missing indexes because
of incorrect stats.

> ORDER BY too_much_seq DESC;

Again.  this is a bit more complex than that.
--
Michael

---

From: James Salsman <[email-masked]>
Date: Thu, 5 Dec 2019 19:14:14 -0800

Thanks, Michael, but I am absolutely convinced that whether a needed
index exists or not is absolutely one of the most run-time
consequential inputs to the query planner. Also, that page is where
people look to optimize, unlike the impenetrable wall-of-text stats
page. Please correct me if I am wrong. Thank you for your
consideration.

Best regards,
Jim