def post(self, database):
payload = json.loads(self.request.body.decode("utf8"))
from_date = payload['from_date']
to_date = payload['to_date']
indexes = []
for ind in payload['indexes']:
hypoind = HypoIndex(ind['nspname'],
ind['relname'],
ind['ams'])
hypoind._ddl = ind['ddl']
indexes.append(hypoind)
queryids = payload['queryids']
powa_conn = self.connect(database="powa")
conn = self.connect(database=database)
queries = list(powa_conn.execute(text("""
SELECT DISTINCT query, ps.queryid
FROM powa_statements ps
WHERE queryid IN :queryids
"""), queryids=tuple(queryids)))
# Create all possible indexes for this qual
hypo_version = self.has_extension("hypopg", database=database)
hypoplans = {}
indbyname = {}
if hypo_version and hypo_version >= "0.0.3":
# identify indexes
# create them
for ind in indexes:
indname = self.execute(
select(["*"])
.select_from(func.hypopg_create_index(ind.ddl)),
database=database).first()[1]
indbyname[indname] = ind
# Build the query and fetch the plans
for query in queries:
querystr = get_any_sample_query(self, database, query.queryid,
from_date,
to_date)
if querystr:
try:
hypoplans[query.queryid] = get_hypoplans(
self.connect(database=database), querystr,
indbyname.values())
except:
# TODO: stop ignoring the error
continue
# To value of a link is the the reduction in cost
self.render_json(hypoplans)
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 post(self, database):
payload = json.loads(self.request.body.decode("utf8"))
from_date = payload['from_date']
to_date = payload['to_date']
indexes = []
for ind in payload['indexes']:
hypoind = HypoIndex(ind['nspname'], ind['relname'], ind['ams'])
hypoind._ddl = ind['ddl']
indexes.append(hypoind)
queryids = payload['queryids']
powa_conn = self.connect(database="powa")
conn = self.connect(database=database)
queries = list(
powa_conn.execute(text("""
SELECT DISTINCT query, ps.queryid
FROM powa_statements ps
WHERE queryid IN :queryids
"""),
queryids=tuple(queryids)))
# Create all possible indexes for this qual
hypo_version = self.has_extension("hypopg", database=database)
hypoplans = {}
indbyname = {}
if hypo_version and hypo_version >= "0.0.3":
# identify indexes
# create them
for ind in indexes:
indname = self.execute(select(["*"]).select_from(
func.hypopg_create_index(ind.ddl)),
database=database).first()[1]
indbyname[indname] = ind
# Build the query and fetch the plans
for query in queries:
querystr = get_any_sample_query(self, database, query.queryid,
from_date, to_date)
if querystr:
try:
hypoplans[query.queryid] = get_hypoplans(
self.connect(database=database), querystr,
indbyname.values())
except:
# TODO: stop ignoring the error
continue
# To value of a link is the the reduction in cost
self.render_json(hypoplans)
def post(self, srvid, database):
try:
# Check remote access first
remote_conn = self.connect(srvid,
database=database,
remote_access=True)
except Exception as e:
raise HTTPError(501,
"Could not connect to remote server: %s" % str(e))
payload = json.loads(self.request.body.decode("utf8"))
from_date = payload['from_date']
to_date = payload['to_date']
indexes = []
for ind in payload['indexes']:
hypoind = HypoIndex(ind['nspname'], ind['relname'], ind['ams'])
hypoind._ddl = ind['ddl']
indexes.append(hypoind)
queryids = payload['queryids']
powa_conn = self.connect(database="powa")
queries = list(
powa_conn.execute(text("""
SELECT DISTINCT query, ps.queryid
FROM powa_statements ps
WHERE srvid = :srvid
AND queryid IN :queryids
"""),
srvid=srvid,
queryids=tuple(queryids)))
# Create all possible indexes for this qual
hypo_version = self.has_extension_version(srvid,
"hypopg",
"0.0.3",
database=database)
hypoplans = {}
indbyname = {}
inderrors = {}
if hypo_version:
# identify indexes
# create them
for ind in indexes:
try:
indname = remote_conn.execute(
select(["*"]).select_from(
func.hypopg_create_index(ind.ddl))).first()[1]
indbyname[indname] = ind
except DBAPIError as e:
inderrors[ind.ddl] = str(e.orig)
continue
except Exception:
# TODO handle other errors?
continue
# Build the query and fetch the plans
for query in queries:
querystr = get_any_sample_query(self, srvid, database,
query.queryid, from_date,
to_date)
if querystr:
try:
hypoplans[query.queryid] = get_hypoplans(
remote_conn, querystr, indbyname.values())
except Exception:
# TODO: stop ignoring the error
continue
# To value of a link is the the reduction in cost
result = {}
result["plans"] = hypoplans
result["inderrors"] = inderrors
self.render_json(result)