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 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")))