Re: Postgres performance comments from a MySQL user

> -----Oorspronkelijk bericht-----
> Van: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] Namens scott.marlowe
> Verzonden: maandag 16 juni 2003 21:00
> Aan: Ernest E Vogelsinger
> CC: Justin Clift; Joseph Shraibman; pgsql-general@postgresql.org
> Onderwerp: Re: [GENERAL] Postgres performance comments from a
> MySQL user
> > I have a query that runs for 17secs for the first time. Now, when
> > executed a second time, it consumes 270 msec. From the
> third execution
> > it's running with 29 msec.
> >
> > However this only holds for the open connection. When
> connecting anew,
> > it starts with 270 msec and drops to 29 msec again.
> >
> > Any clues about this interesting behaviour?
>
> So, once the machine's been up a while, the 17 second
> behaviour goes away
> completely, but the .27 second -> .03 second behaviour remains?
>
> Hmmmm.  Sounds like the very first time you run it the data
> set moves from
> disk to kernel cache.  Then on each new connect postgresql
> pulls it from
> kernel cache to postgresql's buffer, and it's faster still.
Recent postgresql versions (7.3.3 perhaps? Dunno when it got included)
cache the query-plans on the connection that was used for that
connection. So postgres won't have to replan the entire query and that
allows a speedup, although the difference in time is quite large,
perhaps there is more cacheing with the connection than just the
queryplan.

Regards,

Arjen