Estimate time without running the query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Estimate time without running the query

Neto pr-2
Dear all,
Only a doubt.
The Explain <query> command only estimates the cost of execution of a query, and does not estimate time for execution.  
I would like know if exists  some way to estimate the time, without running the query?

Best Regards
[]`s Neto
Reply | Threaded
Open this post in threaded view
|

RE: Estimate time without running the query

Johnes Castro
Hi netoprbr,

Use a command explain analyse.

Best Regards.
Johnes Castro

De: Neto pr <[hidden email]>
Enviado: quinta-feira, 13 de setembro de 2018 19:38
Para: PostgreSQL General
Assunto: Estimate time without running the query
 
Dear all,
Only a doubt.
The Explain <query> command only estimates the cost of execution of a query, and does not estimate time for execution.  
I would like know if exists  some way to estimate the time, without running the query?

Best Regards
[]`s Neto
Reply | Threaded
Open this post in threaded view
|

Re: [External] RE: Estimate time without running the query

Vijaykumar Jain

explain analyze would *run* the query and it can be dangerous if it is a DML statement like insert/update/delete 😊

 

If you still want to go with explain analyze,

You can do

 

begin;

explain analyze <my query>;

rollback;

 

thanks,

Vijay

 

From: Johnes Castro <[hidden email]>
Date: Friday, September 14, 2018 at 3:12 AM
To: Neto pr <[hidden email]>, PostgreSQL General <[hidden email]>
Subject: [External] RE: Estimate time without running the query

 

 

Use a command explain analyse.

 

Best Regards.

Johnes Castro


De: Neto pr <[hidden email]>
Enviado: quinta-feira, 13 de setembro de 2018 19:38
Para: PostgreSQL General
Assunto: Estimate time without running the query

 

Dear all,
Only a doubt.
The Explain <query> command only estimates the cost of execution of a query, and does not estimate time for execution.  
I would like know if exists  some way to estimate the time, without running the query?

Best Regards

[]`s Neto

Reply | Threaded
Open this post in threaded view
|

Re: [External] RE: Estimate time without running the query

Neto pr-2


Em qui, 13 de set de 2018 às 18:49, Vijaykumar Jain <[hidden email]> escreveu:

explain analyze would *run* the query and it can be dangerous if it is a DML statement like insert/update/delete 😊

 

If you still want to go with explain analyze,

You can do

 

begin;

explain analyze <my query>;

rollback;

 


Dear all, 

The problem is that using the explain analyze <query> I have to wait for the query to execute.
I would like to estimate the time without having to wait for the query execution.
Does anyone know how to estimate the time without waiting for the query to be executed?

Best regards
Neto
 

thanks,

Vijay

 

From: Johnes Castro <[hidden email]>
Date: Friday, September 14, 2018 at 3:12 AM
To: Neto pr <[hidden email]>, PostgreSQL General <[hidden email]>
Subject: [External] RE: Estimate time without running the query

 

 

Use a command explain analyse.

 

Best Regards.

Johnes Castro


De: Neto pr <[hidden email]>
Enviado: quinta-feira, 13 de setembro de 2018 19:38
Para: PostgreSQL General
Assunto: Estimate time without running the query

 

Dear all,
Only a doubt.
The Explain <query> command only estimates the cost of execution of a query, and does not estimate time for execution.  
I would like know if exists  some way to estimate the time, without running the query?

Best Regards

[]`s Neto

Reply | Threaded
Open this post in threaded view
|

Re: [External] RE: Estimate time without running the query

David G Johnston
On Thu, Sep 13, 2018 at 3:30 PM, Neto pr <[hidden email]> wrote:
The problem is that using the explain analyze <query> I have to wait for the query to execute.
I would like to estimate the time without having to wait for the query execution.
Does anyone know how to estimate the time without waiting for the query to be executed?

On the machine in question you have to experiment to obtain data to construct a formula to convert cost to time.  Then when using the function remember that lots of things can play into individual executions taking more time (and sometimes less too I suspect) such as locks, caching, physical data locality.

It seems more useful to log actual execution times and look for trends.  If you are writing a query odds are it needs to be run regardless of how efficient it may be - or used in a relative comparison to an alternate query.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: [External] RE: Estimate time without running the query

Neto pr-2


Em qui, 13 de set de 2018 às 19:53, David G. Johnston <[hidden email]> escreveu:
On Thu, Sep 13, 2018 at 3:30 PM, Neto pr <[hidden email]> wrote:
The problem is that using the explain analyze <query> I have to wait for the query to execute.
I would like to estimate the time without having to wait for the query execution.
Does anyone know how to estimate the time without waiting for the query to be executed?

On the machine in question you have to experiment to obtain data to construct a formula to convert cost to time.  Then when using the function remember that lots of things can play into individual executions taking more time (and sometimes less too I suspect) such as locks, caching, physical data locality.

It seems more useful to log actual execution times and look for trends.  If you are writing a query odds are it needs to be run regardless of how efficient it may be - or used in a relative comparison to an alternate query.


Okay, David, but does not it have some SQL statement that returns a time estimate, without having to execute the query?

 
David J.

Reply | Threaded
Open this post in threaded view
|

Re: [External] RE: Estimate time without running the query

Adrian Klaver-4
On 9/13/18 4:55 PM, Neto pr wrote:

>
>
> Em qui, 13 de set de 2018 às 19:53, David G. Johnston
> <[hidden email] <mailto:[hidden email]>> escreveu:
>
>     On Thu, Sep 13, 2018 at 3:30 PM, Neto pr <[hidden email]
>     <mailto:[hidden email]>>wrote:
>
>         The problem is that using the explain analyze <query> I have to
>         wait for the query to execute.
>         I would like to estimate the time without having to wait for the
>         query execution.
>         Does anyone know how to estimate the time without waiting for
>         the query to be executed?
>
>
>     On the machine in question you have to experiment to obtain data to
>     construct a formula to convert cost to time.  Then when using the
>     function remember that lots of things can play into individual
>     executions taking more time (and sometimes less too I suspect) such
>     as locks, caching, physical data locality.
>
>     It seems more useful to log actual execution times and look for
>     trends.  If you are writing a query odds are it needs to be run
>     regardless of how efficient it may be - or used in a relative
>     comparison to an alternate query.
>
>
> Okay, David, but does not it have some SQL statement that returns a time
> estimate, without having to execute the query?

To get close to a true time you need to run the actual query. An analogy
based on running 10K under the following conditions:

1) Cool day, flat course.

2) Hot day, up a 10% grade.

You can reasonably predict that 1) will yield a faster time then 2),
however you will not know the actual times until you run them.

>
>     David J.
>


--
Adrian Klaver
[hidden email]