def get(self, database, query):
if not self.has_extension("pg_qualstats"):
raise HTTPError(501, "PG qualstats is not installed")
condition = text("""datname = :database AND s.queryid = :query
AND coalesce_range && tstzrange(:from, :to)""")
sql = (select(['most_filtering.quals',
'most_filtering.query',
'to_json(most_filtering) as "most filtering"',
'to_json(least_filtering) as "least filtering"',
'to_json(most_executed) as "most executed"'])
.select_from(
qual_constants("most_filtering", condition)
.alias("most_filtering")
.join(
qual_constants("least_filtering", condition)
.alias("least_filtering"),
text("most_filtering.rownumber = "
"least_filtering.rownumber"))
.join(qual_constants("most_executed", condition)
.alias("most_executed"),
text("most_executed.rownumber = "
"least_filtering.rownumber"))))
params = {"database": database, "query": query,
"from": self.get_argument("from"),
"to": self.get_argument("to")}
quals = self.execute(sql, params=params)
plans = []
if quals.rowcount > 0:
row = quals.first()
for key in ('most filtering', 'least filtering', 'most executed'):
vals = row[key]
query = format_jumbled_query(row['query'], vals['constants'])
plan = "N/A"
try:
result = self.execute("EXPLAIN %s" % query,
database=database)
plan = "\n".join(v[0] for v in result)
except:
pass
plans.append(Plan(key, vals['constants'], query,
plan, vals["filter_ratio"], vals['count']))
if len(plans) == 0:
self.flash("No quals found for this query", "warning")
self.render("xhr.html", content="")
return
self.render("database/query/explains.html", plans=plans)
def get(self, database, query):
if not self.has_extension("pg_qualstats"):
raise HTTPError(501, "PG qualstats is not installed")
condition = text("""datname = :database AND s.queryid = :query
AND coalesce_range && tstzrange(:from, :to)""")
sql = (select([text('most_filtering.quals'),
text('most_filtering.query'),
'to_json(most_filtering) as "most filtering"',
'to_json(least_filtering) as "least filtering"',
'to_json(most_executed) as "most executed"'])
.select_from(
qual_constants("most_filtering", condition)
.alias("most_filtering")
.join(
qual_constants("least_filtering", condition)
.alias("least_filtering"),
text("most_filtering.rownumber = "
"least_filtering.rownumber"))
.join(qual_constants("most_executed", condition)
.alias("most_executed"),
text("most_executed.rownumber = "
"least_filtering.rownumber"))))
params = {"database": database, "query": query,
"from": self.get_argument("from"),
"to": self.get_argument("to")}
quals = self.execute(sql, params=params)
plans = []
if quals.rowcount > 0:
row = quals.first()
for key in ('most filtering', 'least filtering', 'most executed'):
vals = row[key]
query = format_jumbled_query(row['query'], vals['constants'])
plan = "N/A"
try:
result = self.execute("EXPLAIN %s" % query,
database=database)
plan = "\n".join(v[0] for v in result)
except:
pass
plans.append(Plan(key, vals['constants'], query,
plan, vals["filter_ratio"], vals['count']))
if len(plans) == 0:
self.flash("No quals found for this query", "warning")
self.render("xhr.html", content="")
return
self.render("database/query/explains.html", plans=plans)
def query(self):
query = (qual_constants("most_used",
text("""
datname = :database AND
s.queryid = :query AND
qn.qualid = :qual AND
coalesce_range && tstzrange(:from, :to)"""), top=10))
base = qualstat_getstatdata()
c = inner_cc(base)
base = base.where(c.queryid == bindparam("query")).alias()
totals = (base.select()
.where((c.qualid == bindparam("qual")) &
(c.queryid == bindparam("query")))).alias()
return (query.alias().select()
.column(totals.c.occurences.label('total_occurences'))
.correlate(query))
def query(self):
query = (qual_constants("most_used",
text("""
datname = :database AND
s.queryid = :query AND
qn.qualid = :qual AND
coalesce_range && tstzrange(:from, :to)"""),
top=10))
base = qualstat_getstatdata()
c = inner_cc(base)
base = base.where(c.queryid == bindparam("query")).alias()
totals = (
base.select().where((c.qualid == bindparam("qual"))
& (c.queryid == bindparam("query")))).alias()
return (query.alias().select().column(
totals.c.occurences.label('total_occurences')).correlate(query))