Re: Predicting query runtime

2016-09-12 15:16 GMT-03:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:

> On Mon, Sep 12, 2016 at 9:03 AM, Vinicius Segalin <vinisegalin(at)gmail(dot)com>
> wrote:
> > Hi everyone,
> >
> > I'm trying to find a way to predict query runtime (I don't need to be
> > extremely precise). I've been reading some papers about it, and people
> are
> > using machine learning to do so. For the feature vector, they use what
> the
> > DBMS's query planner provide, such as operators and their cost. The
> thing is
> > that I haven't found any work using PostgreSQL, so I'm struggling to
> adapt
> > it.
> > My question is if anyone is aware of a work that uses machine learning
> and
> > PostgreSQL to predict query runtime, or maybe some other method to
> perform
> > this.
>
> Well, postgres estimates the query runtime in the form of an expected
> 'cost', where the cost is an arbitrary measure based on time
> complexity of query plan. It shouldn't be too difficult to correlate
> estimated cost to runtime cost.

That's what I though too. At least it makes sense, I guess. But sometimes
logic doesn't work, so I think only giving it a try will say.

> A statistical analysis of that
> correlation would be incredibly useful work although generating sample
> datasets would be a major challenge.
>
> merlin
>

Indeed. I'm using TPC-B along with pgbench to have some data to test (while
I don't have real data), but I'm having a hard time creating queries that
give me (very) different performance results so I can train my ML algorithm.