Thread: please help understand freeing shared buffers

Among following queries, only THREE runs fast enough for me.

I can't understand the logic behind this.

I once thought that shared buffers replacement is independent from
whether the backend which used it is still alive. But is it true?

Seems like shared buffers are  "returned to the pool" or "freed" just
after client disconnects?



$ cat test.sh
sql="explain (analyze,buffers) select getlocationid_faster2('New
York','10003','NY','US',40.73,-73.99);"
psql="psql -X dev postgres"
echo ONE
echo $sql | $psql
echo TWO THREE
echo $sql $sql | $psql
echo FOUR
echo $sql | $psql


$ . test.sh
ONE
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=0) (actual time=3.733..3.735
rows=1 loops=1)
   Buffers: shared hit=294
 Total runtime: 3.769 ms
(3 rows)

TWO THREE
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=0) (actual time=3.717..3.719
rows=1 loops=1)
   Buffers: shared hit=294
 Total runtime: 3.754 ms
(3 rows)

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=0) (actual time=0.521..0.523
rows=1 loops=1)
   Buffers: shared hit=4
 Total runtime: 0.540 ms
(3 rows)

FOUR
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=0) (actual time=3.740..3.742
rows=1 loops=1)
   Buffers: shared hit=294
 Total runtime: 3.777 ms
(3 rows)

=?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, 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.

            regards, tom lane

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