Hi,
Here are three patches for statistics on various objects:
* database: we had statistics on the Databases node but we didn't have them
on each individual database node.
* indexes: we had statistics on each individual index node but we didn't
have them on the Indexes node of each table.
* functions: we didn't have them at all, but it is now needed on 8.4.
I did a patch for each one because I'm not sure they will all be accepted
right away. For the database one, I'm not sure that copying the
DisplayStatistics() function code is the better way to make it work. On the
index one, should I also display statistics for primary key and unique
constraints? it seems it would be a good idea. But it would meant that only
these constraints would be displayed. CHECK constraints, for example, would be
invisible. Comments?
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
Index: pgadmin/include/schema/pgFunction.h
===================================================================
--- pgadmin/include/schema/pgFunction.h (révision 7971)
+++ pgadmin/include/schema/pgFunction.h (copie de travail)
@@ -14,7 +14,6 @@
#include "pgSchema.h"
-class pgCollection;
class pgFunction;
class pgFunctionFactory : public pgSchemaObjFactory
@@ -23,6 +22,7 @@
pgFunctionFactory(const wxChar *tn=0, const wxChar *ns=0, const wxChar *nls=0, const char **img=0);
virtual dlgProperty *CreateDialog(frmMain *frame, pgObject *node, pgObject *parent);
virtual pgObject *CreateObjects(pgCollection *obj, ctlTree *browser, const wxString &restr=wxEmptyString);
+ virtual pgCollection *CreateCollection(pgObject *obj);
pgFunction *AppendFunctions(pgObject *obj, pgSchema *schema, ctlTree *browser, const wxString &restriction);
};
@@ -39,6 +39,7 @@
void ShowTreeDetail(ctlTree *browser, frmMain *form=0, ctlListView *properties=0, ctlSQLBox *sqlPane=0);
bool CanDropCascaded() { return GetSchema()->GetMetaType() != PGM_CATALOG; }
+ void ShowStatistics(frmMain *form, ctlListView *statistics);
virtual bool GetIsProcedure() const {return false; }
@@ -112,6 +113,14 @@
};
+class pgFunctionCollection : public pgSchemaObjCollection
+{
+public:
+ pgFunctionCollection(pgaFactory *factory, pgSchema *sch);
+ void ShowStatistics(frmMain *form, ctlListView *statistics);
+};
+
+
class pgTriggerFunctionFactory : public pgFunctionFactory
{
public:
Index: pgadmin/schema/pgFunction.cpp
===================================================================
--- pgadmin/schema/pgFunction.cpp (révision 7971)
+++ pgadmin/schema/pgFunction.cpp (copie de travail)
@@ -42,6 +42,20 @@
{
}
+void pgFunction::ShowStatistics(frmMain *form, ctlListView *statistics)
+{
+ if (GetConnection()->BackendMinimumVersion(8, 4))
+ {
+ wxString sql=wxT("SELECT calls AS ") + qtIdent(_("Number of calls")) +
+ wxT(", total_time AS ") + qtIdent(_("Total Time")) +
+ wxT(", self_time AS ") + qtIdent(_("Self Time")) +
+ wxT(" FROM pg_stat_user_functions") +
+ wxT(" WHERE schemaname = ") + qtDbString(GetSchema()->GetName()) +
+ wxT(" AND funcname = ") + qtDbString(GetName());
+ DisplayStatistics(statistics, sql);
+ }
+}
+
bool pgFunction::IsUpToDate()
{
wxString sql = wxT("SELECT xmin FROM pg_proc WHERE oid = ") + this->GetOidStr();
@@ -681,6 +695,11 @@
}
+pgCollection *pgFunctionFactory::CreateCollection(pgObject *obj)
+{
+ return new pgFunctionCollection(GetCollectionFactory(), (pgSchema*)obj);
+}
+
pgObject *pgTriggerFunctionFactory::CreateObjects(pgCollection *collection, ctlTree *browser, const wxString &restr)
{
wxString funcRestriction=wxT(
@@ -738,3 +757,46 @@
pgProcedureFactory procedureFactory;
static pgaCollectionFactory cfp(&procedureFactory, __("Procedures"), procedures_xpm);
+
+pgFunctionCollection::pgFunctionCollection(pgaFactory *factory, pgSchema *sch)
+: pgSchemaObjCollection(factory, sch)
+{
+}
+
+
+void pgFunctionCollection::ShowStatistics(frmMain *form, ctlListView *statistics)
+{
+ if (GetConnection()->BackendMinimumVersion(8, 4))
+ {
+ wxLogInfo(wxT("Displaying statistics for functions on ") + GetSchema()->GetName());
+
+ wxString sql=wxT("SELECT funcname, calls, total_time, self_time")
+ wxT(" FROM pg_stat_user_functions")
+ wxT(" WHERE schemaname = ") + qtDbString(GetSchema()->GetName())
+ + wxT(" ORDER BY funcname");
+
+ // Add the statistics view columns
+ statistics->ClearAll();
+ statistics->AddColumn(_("Function"), 60);
+ statistics->AddColumn(_("Calls"), 50);
+ statistics->AddColumn(_("Total Time"), 60);
+ statistics->AddColumn(_("Self Time"), 60);
+
+ pgSet *stats = GetDatabase()->ExecuteSet(sql);
+ if (stats)
+ {
+ long pos=0;
+ while (!stats->Eof())
+ {
+ statistics->InsertItem(pos, stats->GetVal(wxT("funcname")), PGICON_STATISTICS);
+ statistics->SetItem(pos, 1, stats->GetVal(wxT("calls")));
+ statistics->SetItem(pos, 2, stats->GetVal(wxT("total_time")));
+ statistics->SetItem(pos, 3, stats->GetVal(wxT("self_time")));
+ stats->MoveNext();
+ pos++;
+ }
+
+ delete stats;
+ }
+ }
+}
Index: pgadmin/include/schema/pgDatabase.h
===================================================================
--- pgadmin/include/schema/pgDatabase.h (révision 7968)
+++ pgadmin/include/schema/pgDatabase.h (copie de travail)
@@ -44,6 +44,7 @@
void ShowTreeDetail(ctlTree *browser, frmMain *form=0, ctlListView *properties=0, ctlSQLBox *sqlPane=0);
void ShowHint(frmMain *form, bool force);
+ void ShowStatistics(frmMain *form, ctlListView *statistics);
pgSet *ExecuteSet(const wxString& sql);
wxString ExecuteScalar(const wxString& sql);
Index: pgadmin/schema/pgDatabase.cpp
===================================================================
--- pgadmin/schema/pgDatabase.cpp (révision 7968)
+++ pgadmin/schema/pgDatabase.cpp (copie de travail)
@@ -180,6 +180,48 @@
}
+void pgDatabase::ShowStatistics(frmMain *form, ctlListView *statistics)
+{
+ bool hasSize=GetConnection()->HasFeature(FEATURE_SIZE);
+
+ wxString sql=wxT("SELECT numbackends AS ") + qtIdent(_("Backends")) +
+ wxT(", xact_commit AS ") + qtIdent(_("Xact Committed")) +
+ wxT(", xact_rollback AS ") + qtIdent(_("Xact Rolled Back")) +
+ wxT(", blks_read AS ") + qtIdent(_("Blocks Read")) +
+ wxT(", blks_hit AS ") + qtIdent(_("Blocks Hit"));
+
+ if (GetConnection()->BackendMinimumVersion(8,3))
+ sql += wxT(", tup_returned AS ") + qtIdent(_("Tuples Returned")) +
+ wxT(", tup_fetched AS ") + qtIdent(_("Tuples Fetched")) +
+ wxT(", tup_inserted AS ") + qtIdent(_("Tuples Inserted")) +
+ wxT(", tup_updated AS ") + qtIdent(_("Tuples Updated")) +
+ wxT(", tup_deleted AS ") + qtIdent(_("Tuples Deleted"));
+
+ if (hasSize)
+ sql += wxT(", pg_size_pretty(pg_database_size(datid)) AS ") + qtIdent(_("Size"));
+
+ sql += wxT("\n FROM pg_stat_database db WHERE datname=") + qtDbString(GetName());
+
+ // DisplayStatistics is not available for this object
+
+ CreateListColumns(statistics, _("Statistic"), _("Value"));
+
+ pgSet *stats = connection()->ExecuteSet(sql);
+
+ if (stats)
+ {
+ int col;
+ for (col=0 ; col < stats->NumCols() ; col++)
+ {
+ if (!stats->ColName(col).IsEmpty())
+ statistics->AppendItem(stats->ColName(col), stats->GetVal(col));
+ }
+ delete stats;
+ }
+
+}
+
+
pgSet *pgDatabase::ExecuteSet(const wxString& sql)
{
pgSet *set=0;
Index: pgadmin/include/schema/pgIndex.h
===================================================================
--- pgadmin/include/schema/pgIndex.h (révision 7971)
+++ pgadmin/include/schema/pgIndex.h (copie de travail)
@@ -15,7 +15,6 @@
#include "pgTable.h"
#include <wx/arrstr.h>
-class pgCollection;
@@ -117,6 +116,7 @@
{
public:
virtual pgObject *CreateObjects(pgCollection *obj, ctlTree *browser, const wxString &restr=wxEmptyString);
+ virtual pgCollection *CreateCollection(pgObject *obj);
protected:
pgIndexBaseFactory(const wxChar *tn, const wxChar *ns, const wxChar *nls, const char **img) : pgTableObjFactory(tn, ns, nls, img) {}
};
@@ -130,6 +130,14 @@
};
extern pgIndexFactory indexFactory;
+class pgIndexBaseCollection : public pgTableObjCollection
+{
+public:
+ pgIndexBaseCollection(pgaFactory *factory, pgTable *tbl);
+ void ShowStatistics(frmMain *form, ctlListView *statistics);
+};
+
+
class executePgstatindexFactory : public contextActionFactory
{
public:
Index: pgadmin/schema/pgIndex.cpp
===================================================================
--- pgadmin/schema/pgIndex.cpp (révision 7971)
+++ pgadmin/schema/pgIndex.cpp (copie de travail)
@@ -491,6 +491,13 @@
}
+pgCollection *pgIndexBaseFactory::CreateCollection(pgObject *obj)
+{
+ return new pgIndexBaseCollection(GetCollectionFactory(), (pgTable*)obj);
+}
+
+
+
pgObject *pgIndexFactory::CreateObjects(pgCollection *collection, ctlTree *browser, const wxString &restriction)
{
return pgIndexBaseFactory::CreateObjects(collection, browser, restriction + wxT("\n AND conname IS NULL"));
@@ -511,3 +518,62 @@
pgIndexFactory indexFactory;
static pgaCollectionFactory cf(&indexFactory, __("Indexes"), indexes_xpm);
+
+
+pgIndexBaseCollection::pgIndexBaseCollection(pgaFactory *factory, pgTable *tbl)
+: pgTableObjCollection(factory, tbl)
+{
+}
+
+
+void pgIndexBaseCollection::ShowStatistics(frmMain *form, ctlListView *statistics)
+{
+ wxLogInfo(wxT("Displaying statistics for indexes on ") + GetTable()->GetName());
+
+ bool hasSize=GetConnection()->HasFeature(FEATURE_SIZE);
+
+ // Add the statistics view columns
+ statistics->ClearAll();
+ statistics->AddColumn(_("Index Name"));
+ statistics->AddColumn(_("Index Scans"));
+ statistics->AddColumn(_("Index Tuples Read"));
+ statistics->AddColumn(_("Index Tuples Fetched"));
+ if (hasSize)
+ statistics->AddColumn(_("Size"));
+
+ wxString sql = wxT("SELECT indexrelname, ")
+ wxT("idx_scan, idx_tup_read, idx_tup_fetch");
+
+ if (hasSize)
+ sql += wxT(", pg_size_pretty(pg_relation_size(") + GetOidStr() + wxT(")) AS ") + qtIdent(_("size"));
+
+ sql += wxT("\n")
+ wxT(" FROM pg_stat_all_indexes stat\n")
+ wxT(" JOIN pg_class cls ON cls.oid=indexrelid\n")
+ wxT(" LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0')\n")
+ wxT(" LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)\n")
+ wxT(" WHERE schemaname = ") + qtDbString(GetTable()->GetSchema()->GetName())
+ + wxT(" AND stat.relname = ") + qtDbString(GetTable()->GetName())
+ + wxT(" AND con.contype IS NULL")
+ + wxT("\n ORDER BY indexrelname");
+
+ pgSet *stats = GetDatabase()->ExecuteSet(sql);
+
+ if (stats)
+ {
+ long pos=0;
+ while (!stats->Eof())
+ {
+ statistics->InsertItem(pos, stats->GetVal(wxT("indexrelname")), PGICON_STATISTICS);
+ statistics->SetItem(pos, 1, stats->GetVal(wxT("idx_scan")));
+ statistics->SetItem(pos, 2, stats->GetVal(wxT("idx_tup_read")));
+ statistics->SetItem(pos, 3, stats->GetVal(wxT("idx_tup_fetch")));
+ if (hasSize)
+ statistics->SetItem(pos, 4, stats->GetVal(wxT("size")));
+ stats->MoveNext();
+ pos++;
+ }
+
+ delete stats;
+ }
+}
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
