Example #1
0
 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)
Example #2
0
    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)
Example #3
0
    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)
Example #4
0
 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)
Example #5
0
    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)