Re: How long will the query take - Mailing list pgsql-general

From Bill Moran
Subject Re: How long will the query take
Date
Msg-id 20100329131059.0b27cfa3.wmoran@potentialtech.com
Whole thread Raw
In response to Re: How long will the query take  (Andreas Kretschmer <akretschmer@spamfence.net>)
Responses Re: How long will the query take  (John Gage <jsmgage@numericable.fr>)
List pgsql-general
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/

pgsql-general by date:

Previous
From: Steve Atkins
Date:
Subject: Re: Processor speed relative to postgres transactions per second
Next
From: Frans Hals
Date:
Subject: Re: Large index operation crashes postgres