public inbox for [email protected]  

help / color / mirror / Atom feed

From: James Salsman <[email protected]>
To: Michael Paquier <[email protected]>
Cc: [email protected]
Subject: Re: monitoring-stats.html is too impenetrable
Date: Wed, 4 Dec 2019 03:29:55 -0800
Message-ID: <CAD4=uZZRYy8bkedyRipiu0Yp0ZTUszUg6RUTuNnmSs6Y1i2-fA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

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...


On Sun, Nov 24, 2019 at 5:20 PM Michael Paquier <[email protected]> wrote:
>
> On Sun, Nov 24, 2019 at 09:31:58AM +0000, PG Doc comments form wrote:
> > 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.
>
> 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
> > 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
>
> 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

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: monitoring-stats.html is too impenetrable
  In-Reply-To: <CAD4=uZZRYy8bkedyRipiu0Yp0ZTUszUg6RUTuNnmSs6Y1i2-fA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox