please help understand freeing shared buffers

From: Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com>
To: PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: please help understand freeing shared buffers
Date: 2012-01-06 20:24:51
Message-ID: CAP_rww=4AZMvTAnO08siALbMW7nmd_fkYLVN3TEFoQYxFVoj+Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Phoenix Kiula 2012-01-06 21:11:38 Re: URGENT: temporary table not recognized?
Previous Message Bulgrien, Kevin 2012-01-06 19:27:58 Re: DROP ROLE prevented by dependency