Re: does the planner "learn"?

On Mon, Feb 07, 2005 at 11:07:16AM -0800, TJ O'Donnell wrote:
> I understand the value of indexes and of ANALYZE for the efficient use of
> them.
> In the following statement, you can see that the index scan is being used.
> Even though it takes 80 seconds (for a 1.25 million row table), it is
> much faster than without the index.
> But, if I repeat this search, it speeds up by more than a factor of 2!
> I love it, but I'd sure like to understand why. When I do it a third time,
> it speeds up again. A fourth try does not speed it up more.
> Is this speedup due to some memory/disk buffering from which I'm
> benefiting? I'm using linux (actually under VMware on WinXP, so it's even

Yep. Buffering improves performance considerably.

> less
> efficient that it could be on it's own). Or is the planner learning
> something from previous runs of this search? It appears not, since the
> rows it thinks it needs to search are the same in the EXPLAIN ANALYZE
> outputs below. Can someone help me understand why my searches are speeding
> up so I can make it happen the first time, if possible?

Nope, the plan would be the same everytime. There's no way the planner
is doing anything different.
--
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.