public inbox for [email protected]help / color / mirror / Atom feed
monitoring-stats.html is too impenetrable 5+ messages / 3 participants [nested] [flat]
* monitoring-stats.html is too impenetrable @ 2019-11-24 09:31 PG Doc comments form <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: PG Doc comments form @ 2019-11-24 09:31 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: monitoring-stats.html is too impenetrable @ 2019-11-25 01:20 Michael Paquier <[email protected]> parent: PG Doc comments form <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Michael Paquier @ 2019-11-25 01:20 UTC (permalink / raw) To: [email protected]; [email protected] 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 Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: monitoring-stats.html is too impenetrable @ 2019-12-04 11:29 James Salsman <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: James Salsman @ 2019-12-04 11:29 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: [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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: monitoring-stats.html is too impenetrable @ 2019-12-06 03:04 Michael Paquier <[email protected]> parent: James Salsman <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Michael Paquier @ 2019-12-06 03:04 UTC (permalink / raw) To: James Salsman <[email protected]>; +Cc: [email protected] On Wed, Dec 04, 2019 at 03:29:55AM -0800, James Salsman wrote: > 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 ? 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. > 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; Again. this is a bit more complex than that. -- Michael Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: monitoring-stats.html is too impenetrable @ 2019-12-06 03:14 James Salsman <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: James Salsman @ 2019-12-06 03:14 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: [email protected] 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 On Thu, Dec 5, 2019 at 7:05 PM Michael Paquier <[email protected]> wrote: > > On Wed, Dec 04, 2019 at 03:29:55AM -0800, James Salsman wrote: > > 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 ? > > 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. > > > 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; > > Again. this is a bit more complex than that. > -- > Michael ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-12-06 03:14 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-11-24 09:31 monitoring-stats.html is too impenetrable PG Doc comments form <[email protected]> 2019-11-25 01:20 ` Michael Paquier <[email protected]> 2019-12-04 11:29 ` James Salsman <[email protected]> 2019-12-06 03:04 ` Michael Paquier <[email protected]> 2019-12-06 03:14 ` James Salsman <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox