Thread: calculating time

Howdy:

Running PostgreSQL 7.2.1 on RedHat Linux 7.2.

I am not sure if I have asked this before, but,
how can I get the actual time in this statement:

[snip]

Nested Loop  (cost=0.00..5662254656.82 rows=138 width=199)

[/snip]

The cost is supposed to be the amount of time it may take to
do a query, right?  So what, exactly, is this?  500 hours?
minutes?  CPU time?

How do I convert the cost time into regular human time?  Putting
a date param before and after the statement won't really help me
in this situation as I need to figure out if I should even execute it
to begin with.

Thanks!

-X

On Wed, Apr 16, 2003 at 08:19:28AM -0400, Johnson, Shaunn wrote:

> Nested Loop  (cost=0.00..5662254656.82 rows=138 width=199)
>
> The cost is supposed to be the amount of time it may take to
> do a query, right?  So what, exactly, is this?  500 hours?
> minutes?  CPU time?

It's an arbitrary unit that can't be reliably converted into some
real-world time.  You can try using EXPLAIN ANALYZE, which will execute
the query and give you timed costs.  With that data you can also
probably check that the estimates given by the planner are within some
reasonable range.

One thing I've wondered is if one can trust the "conversion" to keep
roughly the same from one query to another, i.e. if I determine the
constants to convert estimates to execution time, can I use that in the
future? (same machine, same shared buffers settings, etc)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"XML!" Exclaimed C++.  "What are you doing here? You're not a programming
language."
"Tell that to the people who use me," said XML.

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> One thing I've wondered is if one can trust the "conversion" to keep
> roughly the same from one query to another,

If the planner's units don't bear some reasonably linear relationship
to the real world, then its cost estimation process doesn't work at all.
So yeah, on any given machine there ought to be a fairly consistent
conversion.

            regards, tom lane

On Wed, Apr 16, 2003 at 08:26:27 -0400,
  Alvaro Herrera <alvherre@dcc.uchile.cl> wrote:
> On Wed, Apr 16, 2003 at 08:19:28AM -0400, Johnson, Shaunn wrote:
>
> > Nested Loop  (cost=0.00..5662254656.82 rows=138 width=199)
> >
> > The cost is supposed to be the amount of time it may take to
> > do a query, right?  So what, exactly, is this?  500 hours?
> > minutes?  CPU time?
>
> It's an arbitrary unit that can't be reliably converted into some
> real-world time.  You can try using EXPLAIN ANALYZE, which will execute
> the query and give you timed costs.  With that data you can also
> probably check that the estimates given by the planner are within some
> reasonable range.

But I wouldn't try it with the sample query since that won't finish in
a reasonable amount of time.

Just to get a rough idea of the scale factor, on my machine the estimated
cost is close to the execution time in milliseconds.