public MultiQueryPostgresCommand(MultiQueryPostgresContext context)
{
this.context = context;
}
private static void GetLatestStatisticsJob(object obj)
{
MultiQueryPostgresContext context = new MultiQueryPostgresContext();
context.Query = "select * from pg_stat_user_tables;select * from pg_stat_user_indexes;select * from pg_stat_user_functions; select * from pg_stat_get_snapshot_timestamp(); select * from pg_stat_reset();";
new MultiQueryPostgresCommand(context).Execute();
DatabasesUsage data = new DatabasesUsage();
string strData = redisDB.StringGet("IndexSuggestionsPOC.DatabasesUsage");
if (!String.IsNullOrEmpty(strData))
{
data = JsonConvert.DeserializeObject <DatabasesUsage>(strData);
}
data.GeneratedDate = context.Records[3].First().pg_stat_get_snapshot_timestamp;
DateTime dayToUse = data.GeneratedDate.Date;
if (!data.DayGenerationExecutions.ContainsKey(dayToUse))
{
data.DayGenerationExecutions.Add(dayToUse, 0);
}
data.DayGenerationExecutions[dayToUse] += 1;
foreach (var tableData in context.Records[0])
{
string dbName = tableData.schemaname;
string relName = tableData.relname;
long seqScansCount = tableData.seq_scan;
long idxScansCount = tableData.idx_scan;
if (!data.Databases.ContainsKey(dbName))
{
data.Databases.Add(dbName, new DatabaseUsage());
}
var database = data.Databases[dbName];
if (!database.Tables.ContainsKey(relName))
{
database.Tables.Add(relName, new TableUsage());
}
var table = database.Tables[relName];
if (idxScansCount > 0)
{
if (!table.IdxScans.ContainsKey(dayToUse))
{
table.IdxScans.Add(dayToUse, 0);
}
table.IdxScans[dayToUse] += tableData.idx_scan;
}
if (seqScansCount > 0)
{
if (!table.SeqScans.ContainsKey(dayToUse))
{
table.SeqScans.Add(dayToUse, 0);
}
table.SeqScans[dayToUse] += tableData.seq_scan;
}
}
foreach (var indexData in context.Records[1])
{
string dbName = indexData.schemaname;
string relName = indexData.relname;
string idxName = indexData.indexrelname;
long scansCount = indexData.idx_scan;
if (!data.Databases.ContainsKey(dbName))
{
data.Databases.Add(dbName, new DatabaseUsage());
}
var database = data.Databases[dbName];
if (!database.Tables.ContainsKey(relName))
{
database.Tables.Add(relName, new TableUsage());
}
if (scansCount > 0)
{
var table = database.Tables[relName];
if (!table.Indices.ContainsKey(idxName))
{
table.Indices.Add(idxName, new IndexUsage());
}
var index = table.Indices[idxName];
if (!index.IdxScans.ContainsKey(dayToUse))
{
index.IdxScans.Add(dayToUse, 0);
}
index.IdxScans[dayToUse] += scansCount;
}
}
foreach (var procedureData in context.Records[2])
{
string dbName = procedureData.schemaname;
string procName = procedureData.funcname;
long calls = procedureData.calls;
if (calls > 0)
{
if (!data.Databases.ContainsKey(dbName))
{
data.Databases.Add(dbName, new DatabaseUsage());
}
var database = data.Databases[dbName];
if (!database.Procedures.ContainsKey(procName))
{
database.Procedures.Add(procName, new ProcedureUsage());
}
var procedure = database.Procedures[procName];
if (!procedure.Calls.ContainsKey(dayToUse))
{
procedure.Calls.Add(dayToUse, 0);
}
procedure.Calls[dayToUse] += calls;
}
}
strData = JsonConvert.SerializeObject(data, Formatting.Indented);
redisDB.StringSet("IndexSuggestionsPOC.DatabasesUsage", strData);
PrintNotUsed(data);
}
