Re: Postgres performance comments from a MySQL user

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
>