Re: please help understand freeing shared buffers

2012/1/6 Tom Lane <tgl@sss.pgh.pa.us>:
> =?UTF-8?Q?Filip_Rembia=C5=82kowski?= <filip.rembialkowski@gmail.com> writes:
>> Among following queries, only THREE runs fast enough for me.
>> I can't understand the logic behind this.
>
> I'm not sure why you'd expect real answers when you haven't shown us
> what the query is doing,

it is an UDF, encapsulating a single SELECT where a=$1 and b=$2 and c=$3

> but my first thought is that the discrepancy
> comes from additional buffer touches in the first execution of a query
> in a given backend; which is not exactly surprising because that backend
> has to load up its system catalog caches.  IOW, the excess touches
> represent accesses to system catalogs not user tables.
>
> In general, if you're annoyed by query execution times measured in
> milliseconds, you'd be best advised not to start a fresh connection
> for each one.  A new connection not only involves a process launch
> but a fair amount of loading of local caches, and a large part of
> the latter work happens during the first few queries it processes.


thank you, that explains a lot.

I misinterpreted the number of buffer hits as true buffer reads.

sure, using persistent connections is what I will do (we have pgbouncer here)


Filip