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)
