Estimate time without running the query

Hi netoprbr,

Use a command explain analyse.

Best Regards.
Johnes Castro
________________________________
De: Neto pr <netoprbr9@gmail.com>
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

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 <johnescm@hotmail.com>
Date: Friday, September 14, 2018 at 3:12 AM
To: Neto pr <netoprbr9@gmail.com>, PostgreSQL General <pgsql-general@postgresql.org>
Subject: [External] RE: Estimate time without running the query

Hi netoprbr,

Use a command explain analyse.

Best Regards.
Johnes Castro
________________________________
De: Neto pr <netoprbr9@gmail.com>
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

Em qui, 13 de set de 2018 às 18:49, Vijaykumar Jain <vjain@opentable.com>
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

On Thu, Sep 13, 2018 at 3:30 PM, Neto pr <netoprbr9@gmail.com> 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.

Em qui, 13 de set de 2018 às 19:53, David G. Johnston <
david.g.johnston@gmail.com> escreveu:

On Thu, Sep 13, 2018 at 3:30 PM, Neto pr <netoprbr9@gmail.com> 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?

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

Em qui, 13 de set de 2018 às 19:53, David G. Johnston
<david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> escreveu:

On Thu, Sep 13, 2018 at 3:30 PM, Neto pr <netoprbr9@gmail.com
<mailto:netoprbr9@gmail.com>>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
adrian.klaver@aklaver.com