Re: Understanding EXPLAIN ANALYZE output

On Wed, Feb 09, 2005 at 03:38:32PM -0700, Ed L. wrote:
> On Wednesday February 9 2005 3:13, Martijn van Oosterhout wrote:
> >
> > In general, the EXPLAIN ANALYZE output follows the planner
> > output as close as possible. If you look at the original query
> > posted, it showed an Index Scan costing 4.63..4.63 which means
> > the index scan is taking (on average) 4.63ms to return 1 row!
> > If it displayed as 4000 or something it would be a lot less
> > clear what was going on.
> >
> > Hope this helps,
>
> Both replies help very much, thanks. Regarding 4.63ms to return
> a row, I know I/O speeds vary widely depending on conditions and
> hardware, but what would you say is a "red flag" value for the
> actual time to retrieve a row. I see that for most of the other
> times in this example, 0.01ms to 0.10ms is not unusual.

That number 4.63ms is way out. Especially considering this is an
*average* over 1000+ iterations which tells you something it very wrong
with that step. Note, Index Scan can take a long time, it completely
depends on how many rows it returns. Also, if there is an additional
filter on the index (someotherfield = 'x') it may take a while to
return a single value.

But for unique indexes on primary keys (which is what this appears to
be) the times should hopefully be <1ms *average* (caching and load are
imprtant factors).

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.