Re: Postgres performance comments from a MySQL user
От | Arjen van der Meijden |
---|---|
Тема | Re: Postgres performance comments from a MySQL user |
Дата | |
Msg-id | 000a01c3345b$74920220$3ac15e91@acm обсуждение исходный текст |
Ответ на | Re: Postgres performance comments from a MySQL user (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Postgres performance comments from a MySQL user
|
Список | pgsql-general |
Well, then there is not. It would still be nice, however, to know why queries are faster the second time they're run, even if there is a 100% cachehit for the first running query. For a group of eleven queries I put together to form a simple benchmark the first run of the batch shows total times like: Total time: 92.174ms Total time: 93.502ms Total time: 92.719ms While second and later runs on the same connection runs like: Total time: 53.162ms Total time: 52.870ms Total time: 52.974ms Total time: 52.855ms These timings do not include forking off the connection and such hidden timeconsumers, just the query and retrieving its results (which don't change over time, nor on different connections). It would be very nice if postgresql was able to get the 53ms timings every time the queries got executed, even if the it's the first run on the connection. Another odd thing here is that even if I'd change some parameter (like looking up the user with uid 20000 instead of 1) it's always faster when it is run as the second query, then when it is run as the first one. But another (like uid 50000) is run as fast as the second query. So if there is no query-plan cacheing (why not? Wouldn't it improve performance?), where do the time differences come from? The silly thing is I really recall having it read somewhere (as an improvement for pgsql 7.4?), but it might have been the cacheing of plpgsql/prepared statements :) Regards, Arjen van der Meijden > Tom Lane wrote: > > Arjen van der Meijden <acm@tweakers.net> writes: > > 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. > > There is absolutely no truth to the above statement. There > is no plan caching (except in plpgsql, which has had it from > the beginning). > > You can get the effect of plan caching with prepared > statements ... but the OP didn't say anything about using PREPARE ... > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend >
В списке pgsql-general по дате отправления: