Thread: How long will the query take

How long will the query take

From
John Gage
Date:
I ran a query out of pgAdmin, and (as I expected) it took a long
time.  In fact, I did not let it finish.  I stopped it after a little
over an hour.

I'm using 8.4.2 on a Mac with a 2.4GHz processor and 2GB of RAM.

My question is: is there a way to tell how close the query is to being
finished.  It would be a great pity if the query would have finished
in the 10 seconds after I quit it, but I had no way of telling.

As a postscript, I would add that the query was undoubtedly too
ambitious.  I have a reduced set version which I will run shortly.
But I am still curious to know if there is a way to tell how much time
is left.

Thanks,

John

Re: How long will the query take

From
"A. Kretschmer"
Date:
In response to John Gage :
> I ran a query out of pgAdmin, and (as I expected) it took a long
> time.  In fact, I did not let it finish.  I stopped it after a little
> over an hour.
>
> I'm using 8.4.2 on a Mac with a 2.4GHz processor and 2GB of RAM.
>
> My question is: is there a way to tell how close the query is to being
> finished.  It would be a great pity if the query would have finished
> in the 10 seconds after I quit it, but I had no way of telling.
>
> As a postscript, I would add that the query was undoubtedly too
> ambitious.  I have a reduced set version which I will run shortly.
> But I am still curious to know if there is a way to tell how much time
> is left.

No, not really. But you can (and should) run EXPLAIN <your query> to
obtain the execution plan for that query, und you can show us this plan
(and the table-definition for all included tables). Maybe someone is able
to tell you what you can do to speed up your query.

And yes, have you tuned your postgresql.conf?


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Re: How long will the query take

From
Bill Moran
Date:
In response to "A. Kretschmer" <andreas.kretschmer@schollglas.com>:

> In response to John Gage :
> > I ran a query out of pgAdmin, and (as I expected) it took a long
> > time.  In fact, I did not let it finish.  I stopped it after a little
> > over an hour.
> >
> > I'm using 8.4.2 on a Mac with a 2.4GHz processor and 2GB of RAM.
> >
> > My question is: is there a way to tell how close the query is to being
> > finished.  It would be a great pity if the query would have finished
> > in the 10 seconds after I quit it, but I had no way of telling.
> >
> > As a postscript, I would add that the query was undoubtedly too
> > ambitious.  I have a reduced set version which I will run shortly.
> > But I am still curious to know if there is a way to tell how much time
> > is left.
>
> No, not really. But you can (and should) run EXPLAIN <your query> to
> obtain the execution plan for that query, und you can show us this plan
> (and the table-definition for all included tables). Maybe someone is able
> to tell you what you can do to speed up your query.

To piggyback on this ... EXPLAIN _is_ the way to know how long your
query will take, but keep in mind it's only an _estimate_.

Given that, in my experience EXPLAIN is pretty accurate 90% of the
time, as long as you analyze frequently enough.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Re: How long will the query take

From
Andreas Kretschmer
Date:
Bill Moran <wmoran@potentialtech.com> wrote:

> > No, not really. But you can (and should) run EXPLAIN <your query> to
> > obtain the execution plan for that query, und you can show us this plan
> > (and the table-definition for all included tables). Maybe someone is able
> > to tell you what you can do to speed up your query.
>
> To piggyback on this ... EXPLAIN _is_ the way to know how long your
> query will take, but keep in mind it's only an _estimate_.
>
> Given that, in my experience EXPLAIN is pretty accurate 90% of the
> time, as long as you analyze frequently enough.

As far as i know, EXPLAIN _can't_ say how long a query will take, it
returns only a COST, not a TIME.

Or can you tell me how long this query will be take?

test=# explain select * from foo;
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on foo  (cost=0.00..34.00 rows=2400 width=4)
(1 Zeile)


Okay, it's a really little table and a really simple plan ... but
imagine, i have a table with 100 millions rows and a) a slow disk and b)
a fast SSD.

You can't say how long the query will runs, even an estimate, okay?


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: How long will the query take

From
Bill Moran
Date:
In response to Andreas Kretschmer <akretschmer@spamfence.net>:

> Bill Moran <wmoran@potentialtech.com> wrote:
>
> > > No, not really. But you can (and should) run EXPLAIN <your query> to
> > > obtain the execution plan for that query, und you can show us this plan
> > > (and the table-definition for all included tables). Maybe someone is able
> > > to tell you what you can do to speed up your query.
> >
> > To piggyback on this ... EXPLAIN _is_ the way to know how long your
> > query will take, but keep in mind it's only an _estimate_.
> >
> > Given that, in my experience EXPLAIN is pretty accurate 90% of the
> > time, as long as you analyze frequently enough.
>
> As far as i know, EXPLAIN _can't_ say how long a query will take, it
> returns only a COST, not a TIME.

Correct.

> Or can you tell me how long this query will be take?
>
> test=# explain select * from foo;
>                       QUERY PLAN
> -------------------------------------------------------
>  Seq Scan on foo  (cost=0.00..34.00 rows=2400 width=4)
> (1 Zeile)

EXPLAIN ANALYZE a few other queries of various complexity, and I'll be
able to translate that estimate to a time.

No, it's not 100% accurate, but (as I stated earlier) in my experience,
it gives you a pretty good idea.

> Okay, it's a really little table and a really simple plan ... but
> imagine, i have a table with 100 millions rows and a) a slow disk and b)
> a fast SSD.

You're absolutely correct, and that's something that I should not have
omitted from my previous response.  Translating the cost into a time
estimate is highly hardware-dependent, and not 100% accurate, so run
some tests to get an idea of what your cost -> time ratio is, and take
those cost estimates with a grain of salt.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Re: How long will the query take

From
John Gage
Date:
I will report back on this and attempt to give the particulars.  It
will take 24 hours due to other time commitments.

Thank you very much for explaining :) this to me.

When I used only the first  10,000 rows of the 100+ thousand rows in
the original table (of two tables) I was working with, I got the
result I wanted in 10 minutes, which was really probably 80% of what I
wanted.  Nevertheless, I do not want to fly blind in the future.

John


On Mar 29, 2010, at 7:10 PM, Bill Moran wrote:

> In response to Andreas Kretschmer <akretschmer@spamfence.net>:
>
>> Bill Moran <wmoran@potentialtech.com> wrote:
>>
>>>> No, not really. But you can (and should) run EXPLAIN <your query>
>>>> to
>>>> obtain the execution plan for that query, und you can show us
>>>> this plan
>>>> (and the table-definition for all included tables). Maybe someone
>>>> is able
>>>> to tell you what you can do to speed up your query.
>>>
>>> To piggyback on this ... EXPLAIN _is_ the way to know how long your
>>> query will take, but keep in mind it's only an _estimate_.
>>>
>>> Given that, in my experience EXPLAIN is pretty accurate 90% of the
>>> time, as long as you analyze frequently enough.
>>
>> As far as i know, EXPLAIN _can't_ say how long a query will take, it
>> returns only a COST, not a TIME.
>
> Correct.
>
>> Or can you tell me how long this query will be take?
>>
>> test=# explain select * from foo;
>>                      QUERY PLAN
>> -------------------------------------------------------
>> Seq Scan on foo  (cost=0.00..34.00 rows=2400 width=4)
>> (1 Zeile)
>
> EXPLAIN ANALYZE a few other queries of various complexity, and I'll be
> able to translate that estimate to a time.
>
> No, it's not 100% accurate, but (as I stated earlier) in my
> experience,
> it gives you a pretty good idea.
>
>> Okay, it's a really little table and a really simple plan ... but
>> imagine, i have a table with 100 millions rows and a) a slow disk
>> and b)
>> a fast SSD.
>
> You're absolutely correct, and that's something that I should not have
> omitted from my previous response.  Translating the cost into a time
> estimate is highly hardware-dependent, and not 100% accurate, so run
> some tests to get an idea of what your cost -> time ratio is, and take
> those cost estimates with a grain of salt.
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general