Обсуждение: Statistics tab, "Tables" node
Hi, When one clicks on the statistics tab with "Tables" node selected, very few statistics are shown : * tuples inserted * tuples updated * tuples deleted * size I'm wondering if we could put all pg_stat_all_tables columns here. That's a lot of informations, but at least some informations seem to be worth it : n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze. I would also like to add some other stuff like the % of the table in cache (if pg_buffercache is available). Comments, ideas ? -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Guillaume Lelarge wrote: > Hi, > > When one clicks on the statistics tab with "Tables" node selected, > very few statistics are shown : > * tuples inserted > * tuples updated > * tuples deleted > * size > > I'm wondering if we could put all pg_stat_all_tables columns here. > That's a lot of informations, but at least some informations seem to > be worth it : n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, > last_analyze, last_autoanalyze. I would also like to add some other > stuff like the % of the table in cache (if pg_buffercache is > available). > > Comments, ideas ? +1 on the stuff from pg_stat (but make sure to check which columns are available in which version) -1 on the pg_buffercache stuff. That one can be populated using a special option I think, but since pg_buffercache takes out some fairly heavy locking, we shouldn't do that by default. //Magnus
Magnus Hagander a écrit : > Guillaume Lelarge wrote: >> [...] >> I'm wondering if we could put all pg_stat_all_tables columns here. >> That's a lot of informations, but at least some informations seem to >> be worth it : n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, >> last_analyze, last_autoanalyze. I would also like to add some other >> stuff like the % of the table in cache (if pg_buffercache is >> available). >> >> Comments, ideas ? > > +1 on the stuff from pg_stat (but make sure to check which columns are > available in which version) > The patch attached takes care of this. > -1 on the pg_buffercache stuff. That one can be populated using a > special option I think, but since pg_buffercache takes out some fairly > heavy locking, we shouldn't do that by default. > I agree, it would be better to have an option (disabled by default). -- Guillaume. http://www.postgresqlfr.org http://dalibo.com Index: pgadmin/schema/pgTable.cpp =================================================================== --- pgadmin/schema/pgTable.cpp (révision 7274) +++ pgadmin/schema/pgTable.cpp (copie de travail) @@ -768,10 +768,27 @@ statistics->AddColumn(_("Tuples inserted"), 50); statistics->AddColumn(_("Tuples updated"), 50); statistics->AddColumn(_("Tuples deleted"), 50); + if (GetConnection()->BackendMinimumVersion(8, 3)) + { + statistics->AddColumn(_("Tuples HOT updated"), 50); + statistics->AddColumn(_("Live tuples"), 50); + statistics->AddColumn(_("Dead tuples"), 50); + } + if (GetConnection()->BackendMinimumVersion(8, 2)) + { + statistics->AddColumn(_("Last vacuum"), 50); + statistics->AddColumn(_("Last autovacuum"), 50); + statistics->AddColumn(_("Last analyze"), 50); + statistics->AddColumn(_("Last autoanalyze"), 50); + } if (hasSize) statistics->AddColumn(_("Size"), 60); wxString sql=wxT("SELECT st.relname, n_tup_ins, n_tup_upd, n_tup_del"); + if (GetConnection()->BackendMinimumVersion(8, 3)) + sql += wxT(", n_tup_hot_upd, n_live_tup, n_dead_tup"); + if (GetConnection()->BackendMinimumVersion(8, 2)) + sql += wxT(", last_vacuum, last_autovacuum, last_analyze, last_autoanalyze"); if (hasSize) sql += wxT(", pg_size_pretty(pg_relation_size(st.relid)") wxT(" + CASE WHEN cl.reltoastrelid = 0 THEN 0 ELSE pg_relation_size(cl.reltoastrelid) + COALESCE((SELECTSUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0) END") @@ -781,20 +798,35 @@ wxT(" JOIN pg_class cl on cl.oid=st.relid\n") wxT(" WHERE schemaname = ") + qtDbString(GetSchema()->GetName()) + wxT("\n ORDER BY relname"); - + pgSet *stats = GetDatabase()->ExecuteSet(sql); if (stats) { long pos=0; + int i; while (!stats->Eof()) { + i = 4; statistics->InsertItem(pos, stats->GetVal(wxT("relname")), PGICON_STATISTICS); statistics->SetItem(pos, 1, stats->GetVal(wxT("n_tup_ins"))); statistics->SetItem(pos, 2, stats->GetVal(wxT("n_tup_upd"))); statistics->SetItem(pos, 3, stats->GetVal(wxT("n_tup_del"))); + if (GetConnection()->BackendMinimumVersion(8, 3)) + { + statistics->SetItem(pos, i++, stats->GetVal(wxT("n_tup_hot_upd"))); + statistics->SetItem(pos, i++, stats->GetVal(wxT("n_live_tup"))); + statistics->SetItem(pos, i++, stats->GetVal(wxT("n_dead_tup"))); + } + if (GetConnection()->BackendMinimumVersion(8, 2)) + { + statistics->SetItem(pos, i++, stats->GetVal(wxT("last_vacuum"))); + statistics->SetItem(pos, i++, stats->GetVal(wxT("last_autovacuum"))); + statistics->SetItem(pos, i++, stats->GetVal(wxT("last_analyze"))); + statistics->SetItem(pos, i++, stats->GetVal(wxT("last_autoanalyze"))); + } if (hasSize) - statistics->SetItem(pos, 4, stats->GetVal(wxT("size"))); + statistics->SetItem(pos, i, stats->GetVal(wxT("size"))); stats->MoveNext(); pos++; }
On Mon, May 5, 2008 at 4:38 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > > > > +1 on the stuff from pg_stat (but make sure to check which columns are > > available in which version) > > > > > > The patch attached takes care of this. It looks good to me, except the individual table stats should also show the new data (ie. when you click on a single table). BTW, HOT update count is cool - didn't know we added that :-) The display is getting a little wide - I think it's OK for now, but if anything else gets added we need to think about better ways to present the data I think. What might be a nice enhancement, is some auto-sizing based on column heading width. Wanna take a peek at that as well? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page a écrit : > On Mon, May 5, 2008 at 4:38 PM, Guillaume Lelarge > <guillaume@lelarge.info> wrote: >>> +1 on the stuff from pg_stat (but make sure to check which columns are >>> available in which version) >>> >>> >> The patch attached takes care of this. > > It looks good to me, except the individual table stats should also > show the new data (ie. when you click on a single table). BTW, HOT > update count is cool - didn't know we added that :-) > Done. See attached patch. > The display is getting a little wide - I think it's OK for now, but if > anything else gets added we need to think about better ways to present > the data I think. > I completely agree. > What might be a nice enhancement, is some auto-sizing based on column > heading width. Wanna take a peek at that as well? > I'll get a look at it tonight. Can I apply this patch first and then work on the "auto-sizing columns" patch ? -- Guillaume. http://www.postgresqlfr.org http://dalibo.com Index: pgadmin/schema/pgTable.cpp =================================================================== --- pgadmin/schema/pgTable.cpp (révision 7278) +++ pgadmin/schema/pgTable.cpp (copie de travail) @@ -768,10 +768,27 @@ statistics->AddColumn(_("Tuples inserted"), 50); statistics->AddColumn(_("Tuples updated"), 50); statistics->AddColumn(_("Tuples deleted"), 50); + if (GetConnection()->BackendMinimumVersion(8, 3)) + { + statistics->AddColumn(_("Tuples HOT updated"), 50); + statistics->AddColumn(_("Live tuples"), 50); + statistics->AddColumn(_("Dead tuples"), 50); + } + if (GetConnection()->BackendMinimumVersion(8, 2)) + { + statistics->AddColumn(_("Last vacuum"), 50); + statistics->AddColumn(_("Last autovacuum"), 50); + statistics->AddColumn(_("Last analyze"), 50); + statistics->AddColumn(_("Last autoanalyze"), 50); + } if (hasSize) statistics->AddColumn(_("Size"), 60); wxString sql=wxT("SELECT st.relname, n_tup_ins, n_tup_upd, n_tup_del"); + if (GetConnection()->BackendMinimumVersion(8, 3)) + sql += wxT(", n_tup_hot_upd, n_live_tup, n_dead_tup"); + if (GetConnection()->BackendMinimumVersion(8, 2)) + sql += wxT(", last_vacuum, last_autovacuum, last_analyze, last_autoanalyze"); if (hasSize) sql += wxT(", pg_size_pretty(pg_relation_size(st.relid)") wxT(" + CASE WHEN cl.reltoastrelid = 0 THEN 0 ELSE pg_relation_size(cl.reltoastrelid) + COALESCE((SELECTSUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0) END") @@ -781,20 +798,35 @@ wxT(" JOIN pg_class cl on cl.oid=st.relid\n") wxT(" WHERE schemaname = ") + qtDbString(GetSchema()->GetName()) + wxT("\n ORDER BY relname"); - + pgSet *stats = GetDatabase()->ExecuteSet(sql); if (stats) { long pos=0; + int i; while (!stats->Eof()) { + i = 4; statistics->InsertItem(pos, stats->GetVal(wxT("relname")), PGICON_STATISTICS); statistics->SetItem(pos, 1, stats->GetVal(wxT("n_tup_ins"))); statistics->SetItem(pos, 2, stats->GetVal(wxT("n_tup_upd"))); statistics->SetItem(pos, 3, stats->GetVal(wxT("n_tup_del"))); + if (GetConnection()->BackendMinimumVersion(8, 3)) + { + statistics->SetItem(pos, i++, stats->GetVal(wxT("n_tup_hot_upd"))); + statistics->SetItem(pos, i++, stats->GetVal(wxT("n_live_tup"))); + statistics->SetItem(pos, i++, stats->GetVal(wxT("n_dead_tup"))); + } + if (GetConnection()->BackendMinimumVersion(8, 2)) + { + statistics->SetItem(pos, i++, stats->GetVal(wxT("last_vacuum"))); + statistics->SetItem(pos, i++, stats->GetVal(wxT("last_autovacuum"))); + statistics->SetItem(pos, i++, stats->GetVal(wxT("last_analyze"))); + statistics->SetItem(pos, i++, stats->GetVal(wxT("last_autoanalyze"))); + } if (hasSize) - statistics->SetItem(pos, 4, stats->GetVal(wxT("size"))); + statistics->SetItem(pos, i, stats->GetVal(wxT("size"))); stats->MoveNext(); pos++; } @@ -816,9 +848,18 @@ wxT(", idx_scan AS ") + qtIdent(_("Index Scans")) + wxT(", idx_tup_fetch AS ") + qtIdent(_("Index Tuples Fetched"))+ wxT(", n_tup_ins AS ") + qtIdent(_("Tuples Inserted"))+ - wxT(", n_tup_upd AS ") + qtIdent(_("Tuples Updated")) + - wxT(", n_tup_del AS ") + qtIdent(_("Tuples Deleted")) + - wxT(", heap_blks_read AS ") + qtIdent(_("Heap Blocks Read")) + + wxT(", n_tup_upd AS ") + qtIdent(_("Tuples Updated"))+ + wxT(", n_tup_del AS ") + qtIdent(_("Tuples Deleted")); + + if (GetConnection()->BackendMinimumVersion(8, 3)) + { + sql += + wxT(", n_tup_hot_upd AS ") + qtIdent(_("Tuples HOT Updated"))+ + wxT(", n_live_tup AS ") + qtIdent(_("Live Tuples"))+ + wxT(", n_dead_tup AS ") + qtIdent(_("Dead Tuples")); + } + + sql += wxT(", heap_blks_read AS ") + qtIdent(_("Heap Blocks Read")) + wxT(", heap_blks_hit AS ") + qtIdent(_("Heap Blocks Hit")) + wxT(", idx_blks_read AS ") + qtIdent(_("Index Blocks Read")) + wxT(", idx_blks_hit AS ") + qtIdent(_("Index Blocks Hit")) +
On Tue, May 6, 2008 at 2:49 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > > What might be a nice enhancement, is some auto-sizing based on column > > heading width. Wanna take a peek at that as well? > > > I'll get a look at it tonight. Can I apply this patch first and then work on > the "auto-sizing columns" patch ? Sure, go ahead. Thanks, Dave -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page a écrit : > On Tue, May 6, 2008 at 2:49 PM, Guillaume Lelarge > <guillaume@lelarge.info> wrote: >>> What might be a nice enhancement, is some auto-sizing based on column >>> heading width. Wanna take a peek at that as well? >>> >> I'll get a look at it tonight. Can I apply this patch first and then work on >> the "auto-sizing columns" patch ? > > Sure, go ahead. > Done, thanks. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Dave Page a écrit : > [...] > What might be a nice enhancement, is some auto-sizing based on column > heading width. Wanna take a peek at that as well? > I finally found some time to work on this. wxwidgets allows to specify two "default" values : * wxLIST_AUTOSIZE * wxLIST_AUTOSIZE_USEHEADER The last one does what we want, but our ctlListView::AddColumn method's handling of default values was buggy. The attached patch fixes this, and changes pgTableCollection::ShowStatistics method to handle the new default value (wxLIST_AUTOSIZE_USEHEADER). Do we want this for the 1.8 branch ? Obviously, this is a bug fix, but for the fix to really work, we need more changes (ie using the default value in each, at least some of them, call to ctlListView::AddColumn). Comments? -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Guillaume Lelarge a écrit : > [...] > The last one does what we want, but our ctlListView::AddColumn method's > handling of default values was buggy. The attached patch fixes this, and > changes pgTableCollection::ShowStatistics method to handle the new > default value (wxLIST_AUTOSIZE_USEHEADER). > /me wishes he has a mail client that would tell him he forgot the "attached patch" :-/ -- Guillaume. http://www.postgresqlfr.org http://dalibo.com Index: pgadmin/ctl/ctlListView.cpp =================================================================== --- pgadmin/ctl/ctlListView.cpp (révision 7285) +++ pgadmin/ctl/ctlListView.cpp (copie de travail) @@ -45,7 +45,14 @@ void ctlListView::AddColumn(const wxChar *text, int size, int format) { - InsertColumn(GetColumnCount(), text, format, ConvertDialogToPixels(wxPoint(size,0)).x); + if (size == wxLIST_AUTOSIZE || size == wxLIST_AUTOSIZE_USEHEADER) + { + InsertColumn(GetColumnCount(), text, format, size); + } + else + { + InsertColumn(GetColumnCount(), text, format, ConvertDialogToPixels(wxPoint(size,0)).x); + } } Index: pgadmin/include/ctl/ctlListView.h =================================================================== --- pgadmin/include/ctl/ctlListView.h (révision 7285) +++ pgadmin/include/ctl/ctlListView.h (copie de travail) @@ -28,7 +28,7 @@ void CreateColumns(wxImageList *images, const wxString &left, const wxString &right, int leftSize=60); - void AddColumn(const wxChar *text, int size=-1, int format=wxLIST_FORMAT_LEFT); + void AddColumn(const wxChar *text, int size=wxLIST_AUTOSIZE_USEHEADER, int format=wxLIST_FORMAT_LEFT); long AppendItem(int icon, const wxChar *val, const wxChar *val2=0, const wxChar *val3=0); long AppendItem(const wxChar *val, const wxChar *val2=0, const wxChar *val3=0) Index: pgadmin/schema/pgTable.cpp =================================================================== --- pgadmin/schema/pgTable.cpp (révision 7285) +++ pgadmin/schema/pgTable.cpp (copie de travail) @@ -764,25 +764,25 @@ // Add the statistics view columns statistics->ClearAll(); - statistics->AddColumn(_("Table"), 100); - statistics->AddColumn(_("Tuples inserted"), 50); - statistics->AddColumn(_("Tuples updated"), 50); - statistics->AddColumn(_("Tuples deleted"), 50); + statistics->AddColumn(_("Table Name")); + statistics->AddColumn(_("Tuples inserted")); + statistics->AddColumn(_("Tuples updated")); + statistics->AddColumn(_("Tuples deleted")); if (GetConnection()->BackendMinimumVersion(8, 3)) { - statistics->AddColumn(_("Tuples HOT updated"), 50); - statistics->AddColumn(_("Live tuples"), 50); - statistics->AddColumn(_("Dead tuples"), 50); + statistics->AddColumn(_("Tuples HOT updated")); + statistics->AddColumn(_("Live tuples")); + statistics->AddColumn(_("Dead tuples")); } if (GetConnection()->BackendMinimumVersion(8, 2)) { - statistics->AddColumn(_("Last vacuum"), 50); - statistics->AddColumn(_("Last autovacuum"), 50); - statistics->AddColumn(_("Last analyze"), 50); - statistics->AddColumn(_("Last autoanalyze"), 50); + statistics->AddColumn(_("Last vacuum")); + statistics->AddColumn(_("Last autovacuum")); + statistics->AddColumn(_("Last analyze")); + statistics->AddColumn(_("Last autoanalyze")); } if (hasSize) - statistics->AddColumn(_("Size"), 60); + statistics->AddColumn(_("Size")); wxString sql=wxT("SELECT st.relname, n_tup_ins, n_tup_upd, n_tup_del"); if (GetConnection()->BackendMinimumVersion(8, 3))
On Mon, May 12, 2008 at 10:21 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > Guillaume Lelarge a écrit : > > > [...] > > > > The last one does what we want, but our ctlListView::AddColumn method's > handling of default values was buggy. The attached patch fixes this, and > changes pgTableCollection::ShowStatistics method to handle the new default > value (wxLIST_AUTOSIZE_USEHEADER). > > > > > > /me wishes he has a mail client that would tell him he forgot the "attached > patch" :-/ Yeah, me too :-) Patch looks good to my eyeballs - please apply. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page a écrit : > On Mon, May 12, 2008 at 10:21 AM, Guillaume Lelarge > <guillaume@lelarge.info> wrote: >> Guillaume Lelarge a écrit : >> >>> [...] >>> >>> The last one does what we want, but our ctlListView::AddColumn method's >> handling of default values was buggy. The attached patch fixes this, and >> changes pgTableCollection::ShowStatistics method to handle the new default >> value (wxLIST_AUTOSIZE_USEHEADER). >>> >> /me wishes he has a mail client that would tell him he forgot the "attached >> patch" :-/ > > Yeah, me too :-) > AFAIK, gmail does this. Unfortunately, my Thunderbird is less smart. > Patch looks good to my eyeballs - please apply. > Thanks, done. Now working on the refresh code. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com