From: | Arjen van der Meijden <acm(at)tweakers(dot)net> |
---|---|
To: | "'scott(dot)marlowe'" <scott(dot)marlowe(at)ihs(dot)com>, 'Ernest E Vogelsinger' <ernest(at)vogelsinger(dot)at> |
Cc: | 'Justin Clift' <justin(at)postgresql(dot)org>, 'Joseph Shraibman' <jks(at)selectacast(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres performance comments from a MySQL user |
Date: | 2003-06-16 19:33:51 |
Message-ID: | 005a01c3343e$380193b0$3ac15e91@acm |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> -----Oorspronkelijk bericht-----
> Van: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] Namens scott.marlowe
> Verzonden: maandag 16 juni 2003 21:00
> Aan: Ernest E Vogelsinger
> CC: Justin Clift; Joseph Shraibman; pgsql-general(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | John Smith | 2003-06-16 19:35:11 | DBD::PgPP::CursorResponse |
Previous Message | scott.marlowe | 2003-06-16 19:32:15 | Re: postgreSQL on NAS/SAN? |