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 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 post_process(self, data, database, query, **kwargs): conn = self.connect(database=database) data["data"] = resolve_quals(conn, data["data"]) for qual in data["data"]: qual.url = self.reverse_url('QualOverview', database, query, qual.qualid) return data
def post_process(self, data, server, database, query, **kwargs): try: 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)) data["data"] = resolve_quals(remote_conn, data["data"]) for qual in data["data"]: qual.url = self.reverse_url('QualOverview', server, database, query, qual.qualid) return data
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 post_process(self, data, database, query, **kwargs): conn = self.connect(database=database) data["data"] = resolve_quals(conn, data["data"]) return data
def post_process(self, data, database, **kwargs): conn = self.connect(database=database) data["data"] = resolve_quals(conn, data["data"]) return data
def post_process(self, data, server, database, **kwargs): conn = self.connect(server, database=database, remote_access=True) data["data"] = resolve_quals(conn, data["data"]) return data