Re: Predicting query runtime

I’ve been looking for this on postgres too. Does Postgres have something similar to Oracle’s v$session_longops? It gives info on total unit of work, units done so far, last update time, and time remaining etc, and I found it valuable in providing an estimate to how long a certain query would keep running and whether or not to kill it if applicable. This should be relatively easy to implement in postgres too if it is not available yet?

Thanks,
Patricia

From: Oleksandr Shulgin [mailto:oleksandr(dot)shulgin(at)zalando(dot)de]
Sent: Monday, September 12, 2016 11:08 AM
To: Vinicius Segalin
Cc: pgsql general
Subject: Re: Predicting query runtime

On Mon, Sep 12, 2016 at 4:03 PM, Vinicius Segalin <vinisegalin(at)gmail(dot)com<mailto: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.

Hi,

I'm not aware of machine-learning techniques to achieve that (and I don't actually believe it's feasible), but there you might find this extension particularly useful: https://www.postgresql.org/docs/9.5/static/pgstatstatements.html[postgresql.org]<https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_9.5_static_pgstatstatements.html&d=DQMFaQ&c=XK1GVu0Y2HvWRiFNJ9Hesw&r=zPpPZuyVrIaTddPjC9L1HnoccrGeuqog6vsO1YNaFI4&m=ZqAoXnDciOjONogHYk3bTw5Zf1C0cOuDc7uAOa2SCrs&s=J9ht318lQP9Kmn-ptz_cy_sc7FOvMOUqAn61PIOxULQ&e=>

Can you share some links to the papers you are referring to (assuming these are publicly available)?

Regards,
--
Alex

Confidentiality Notice:: This email, including attachments, may include non-public, proprietary, confidential or legally privileged information. If you are not an intended recipient or an authorized agent of an intended recipient, you are hereby notified that any dissemination, distribution or copying of the information contained in or transmitted with this e-mail is unauthorized and strictly prohibited. If you have received this email in error, please notify the sender by replying to this message and permanently delete this e-mail, its attachments, and any copies of it immediately. You should not retain, copy or use this e-mail or any attachment for any purpose, nor disclose all or any part of the contents to any other person. Thank you.