Re: How long will the query take

From: John Gage <jsmgage(at)numericable(dot)fr>
To: Bill Moran <wmoran(at)potentialtech(dot)com>
Cc: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>, pgsql-general(at)postgresql(dot)org
Subject: Re: How long will the query take
Date: 2010-03-29 18:12:47
Message-ID: E6AA9845-FD50-44D1-BDD0-21C2EB68C354@numericable.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)spamfence(dot)net>:
>
>> Bill Moran <wmoran(at)potentialtech(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Greg Smith 2010-03-29 18:20:41 Re: Processor speed relative to postgres transactions per second
Previous Message Mark Vantzelfde 2010-03-29 17:54:50 Re: PostgreSQL on Windows