def get(self, database, query, qual):
stmt = qualstat_getstatdata()
c = inner_cc(stmt)
stmt = stmt.alias()
stmt = (stmt.select().where((c.qualid == bindparam("qualid")) & (
c.queryid == bindparam("query"))).where(stmt.c.count > 0).column(
(c.queryid == bindparam("query")).label("is_my_query")))
quals = list(
self.execute(stmt,
params={
"query": query,
"from": self.get_argument("from"),
"to": self.get_argument("to"),
"qualid": qual
}))
quals = resolve_quals(self.connect(database=database), quals)
my_qual = None
other_queries = {}
for qual in quals:
if qual['is_my_query']:
my_qual = qual
else:
other_queries[qual['queryid']] = qual['query']
if my_qual is None:
self.render("xhr.html", content="nodata")
return
self.render("database/query/qualdetail.html",
qual=my_qual,
database=database,
other_queries=other_queries)
def query(self):
pq = qualstat_getstatdata(column("eval_type") == "f")
base = alias(pq)
query = (select([
func.array_agg(column("queryid")).label("queryids"),
"qualid",
cast(column("quals"), JSONB).label('quals'),
"occurences",
"execution_count",
func.array_agg(column("query")).label("queries"),
"avg_filter",
"filter_ratio"
]).select_from(
join(base, powa_databases,
onclause=(
powa_databases.c.oid == literal_column("dbid"))))
.where(powa_databases.c.datname == bindparam("database"))
.where(column("avg_filter") > 1000)
.where(column("filter_ratio") > 0.3)
.group_by(column("qualid"), column("execution_count"),
column("occurences"),
cast(column("quals"), JSONB),
column("avg_filter"), column("filter_ratio"))
.order_by(column("occurences").desc())
.limit(200))
return query
def get(self, database, query, qual):
stmt = qualstat_getstatdata()
c = inner_cc(stmt)
stmt = stmt.alias()
stmt = (stmt.select()
.where((c.qualid == bindparam("qualid")))
.where(stmt.c.occurences > 0)
.column((c.queryid == bindparam("query")).label("is_my_query")))
quals = list(self.execute(
stmt,
params={"query": query,
"from": self.get_argument("from"),
"to": self.get_argument("to"),
"qualid": qual}))
my_qual = None
other_queries = {}
for qual in quals:
if qual['is_my_query']:
my_qual = resolve_quals(self.connect(database=database),
[qual])[0]
else:
other_queries[qual['queryid']] = qual['query']
if my_qual is None:
self.render("xhr.html", content="nodata")
return
self.render("database/query/qualdetail.html",
qual=my_qual,
database=database,
other_queries=other_queries)
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))
def get(self, database, query):
if not self.has_extension("pg_qualstats"):
raise HTTPError(501, "PG qualstats is not installed")
base_query = qualstat_getstatdata()
c = inner_cc(base_query)
base_query.append_from(text("""LATERAL unnest(quals) as qual"""))
base_query = (base_query.where(c.queryid == query).having(
func.bool_or(column('eval_type') == 'f')).having(
c.execution_count > 1000).having(c.occurences > 0).having(
c.filter_ratio > 0.5).params(**{
'from': '-infinity',
'to': 'infinity'
}))
optimizable = list(self.execute(base_query, params={'query': query}))
optimizable = resolve_quals(self.connect(database=database),
optimizable, 'quals')
hypoplan = None
indexes = {}
for qual in optimizable:
indexes[qual.where_clause] = possible_indexes(qual)
hypo_version = self.has_extension("hypopg", database=database)
if indexes and hypo_version and hypo_version >= "0.0.3":
# identify indexes
# create them
allindexes = [
ind for indcollection in indexes.values()
for ind in indcollection
]
for ind in allindexes:
ddl = ind.hypo_ddl
if ddl is not None:
ind.name = self.execute(ddl, database=database).scalar()
# Build the query and fetch the plans
querystr = get_any_sample_query(self, database, query,
self.get_argument("from"),
self.get_argument("to"))
try:
hypoplan = get_hypoplans(self.connect(database=database),
querystr, allindexes)
except:
# TODO: offer the possibility to fill in parameters from the UI
self.flash("We couldn't get plans for this query, presumably "
"because some parameters are missing ")
self.render("database/query/indexes.html",
indexes=indexes,
hypoplan=hypoplan)
def get(self, database, query):
if not self.has_extension("pg_qualstats"):
raise HTTPError(501, "PG qualstats is not installed")
base_query = qualstat_getstatdata()
c = inner_cc(base_query)
base_query.append_from(text("""LATERAL unnest(quals) as qual"""))
base_query = (base_query
.where(c.queryid == query)
.having(func.bool_or(column('eval_type') == 'f'))
.having(c.execution_count > 1000)
.having(c.occurences > 0)
.having(c.filter_ratio > 0.5)
.params(**{'from': '-infinity',
'to': 'infinity'}))
optimizable = list(self.execute(base_query, params={'query': query}))
optimizable = resolve_quals(self.connect(database=database),
optimizable,
'quals')
hypoplan = None
indexes = {}
for qual in optimizable:
indexes[qual.where_clause] = possible_indexes(qual)
hypo_version = self.has_extension("hypopg", database=database)
if hypo_version and hypo_version >= "0.0.3":
# identify indexes
# create them
allindexes = [ind for indcollection in indexes.values()
for ind in indcollection]
for ind in allindexes:
ddl = ind.hypo_ddl
if ddl is not None:
ind.name = self.execute(ddl, database=database).scalar()
# Build the query and fetch the plans
querystr = get_any_sample_query(self, database, query,
self.get_argument("from"),
self.get_argument("to"))
try:
hypoplan = get_hypoplans(self.connect(database=database), querystr,
allindexes)
except:
# TODO: offer the possibility to fill in parameters from the UI
self.flash("We couldn't get plans for this query, presumably "
"because some parameters are missing ")
self.render("database/query/indexes.html", indexes=indexes,
hypoplan=hypoplan)
def get(self, server, database, query, qual):
try:
# Check remote access first
remote_conn = self.connect(server,
database=database,
remote_access=True)
except Exception as e:
raise HTTPError(501,
"Could not connect to remote server: %s" % str(e))
stmt = qualstat_getstatdata(server)
c = inner_cc(stmt)
stmt = stmt.alias()
stmt = (stmt.select().where((c.qualid == bindparam("qualid"))).where(
stmt.c.occurences > 0).column(
(c.queryid == bindparam("query")).label("is_my_query")))
quals = list(
self.execute(stmt,
params={
"server": server,
"query": query,
"from": self.get_argument("from"),
"to": self.get_argument("to"),
"queryids": [query],
"qualid": qual
}))
my_qual = None
other_queries = {}
for qual in quals:
if qual['is_my_query']:
my_qual = resolve_quals(remote_conn, [qual])[0]
if my_qual is None:
self.render("xhr.html", content="No data")
return
self.render("database/query/qualdetail.html",
qual=my_qual,
database=database,
server=server)
def get(self, database, query):
if not self.has_extension("pg_qualstats"):
raise HTTPError(501, "PG qualstats is not installed")
base_query = qualstat_getstatdata()
c = inner_cc(base_query)
base_query.append_from(text("""LATERAL unnest(quals) as qual"""))
base_query = (base_query
.where(c.queryid == query)
.having(func.bool_or(column('eval_type') == 'f'))
.having(c.count > 1000)
.having(c.filter_ratio > 0.5)
.params(**{'from': '-infinity',
'to': 'infinity'}))
optimizable = list(self.execute(base_query, params={'query': query}))
optimizable = resolve_quals(self.connect(database=database),
optimizable,
'quals')
qual_indexes = {}
for line in optimizable:
qual_indexes[line['where_clause']] = possible_indexes(
line['quals'])
self.render("database/query/indexes.html", indexes=qual_indexes)
def get(self, database, query):
if not self.has_extension("pg_qualstats"):
raise HTTPError(501, "PG qualstats is not installed")
base_query = qualstat_getstatdata()
c = inner_cc(base_query)
base_query.append_from(text("""LATERAL unnest(quals) as qual"""))
base_query = (base_query
.where(c.queryid == query)
.having(func.bool_or(column('eval_type') == 'f'))
.having(c.count > 1000)
.having(c.filter_ratio > 0.5)
.params(**{'from': '-infinity',
'to': 'infinity'}))
optimizable = list(self.execute(base_query, params={'query': query}))
optimizable = resolve_quals(self.connect(database=database),
optimizable,
'quals')
qual_indexes = {}
for line in optimizable:
qual_indexes[line['where_clause']] = possible_indexes(
line['quals'])
self.render("database/query/indexes.html", indexes=qual_indexes)
def query(self):
pq = qualstat_getstatdata(bindparam("server"),
column("eval_type") == "f")
base = alias(pq)
query = (
select([
# queryid in pg11+ is int64, so the value can exceed javascript's
# Number.MAX_SAFE_INTEGER, which mean that the value can get
# truncated by the browser, leading to looking for unexisting
# queryid when processing this data. To avoid that, simply cast
# the value to text.
func.array_agg(cast(column("queryid"),
TEXT)).label("queryids"),
column("qualid"),
cast(column("quals"), JSONB).label('quals'),
column("occurences"),
column("execution_count"),
func.array_agg(column("query")).label("queries"),
column("avg_filter"),
column("filter_ratio")
]).select_from(
join(base,
powa_databases,
onclause=(powa_databases.c.oid == literal_column("dbid")
and powa_databases.c.srvid
== literal_column("srvid")))).
where(powa_databases.c.datname == bindparam("database")).where(
powa_databases.c.srvid == bindparam("server")).where(
column("avg_filter") > 1000).where(
column("filter_ratio") > 0.3).group_by(
column("qualid"), column("execution_count"),
column("occurences"), cast(column("quals"), JSONB),
column("avg_filter"),
column("filter_ratio")).order_by(
column("occurences").desc()).limit(200))
return query
def query(self):
base = qualstat_getstatdata()
c = inner_cc(base)
return (base.where(c.queryid == bindparam("query")))
