Re: postgresql shared buffers

Let me explain once more.

I have two relations which are 10 times more than bufferpool
size.I have observed the following things when joined that two
relations(it using merge join to join both relations)

1.It first accessed system catalog tables
2.Relation 1
3.Relation 2

my doubt is one whole relation cant fit in the main memory.That too when
we use merge join, it should keep some part of 1st relations and should
scan second relation as bufferpool size is less compared to size
of each relation.similarly for the remainin part of 1st relation.But
it is not happening here.First whole Relation1 is scanned and then
Relation 2 is scanned. Then how is it joining two relations using merge
join? Am I missing something?
I traced scanning of relation by editing the functions ReadBuffer() and
BufferAlloc(),StrategyGetBuffer().

I hope now it is clear.

thanks in anticipation.

bye

On Fri, 8 Sep 2006, Heikki Linnakangas wrote:

> Date: Fri, 08 Sep 2006 14:30:01 +0100
> From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
> To: Praveen Kumar N <praveen_n(at)students(dot)iiit(dot)net>
> Cc: pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] postgresql shared buffers
>
> Praveen Kumar N wrote:
>> I have installed postgresql from sourcecode.I would like to know
>> how pages are replaced in the bufferpool when we join two relations.I tried
>> to trace it by editing files
>> pgsql/src/backend/storage/buffer/(bufmgr.c,freelist.c).But i feel still I
>> am missing some information after observing extracted information abt
>> buffer replacement.My input datasize is 10times more than main memory/RAM
>> size.When I joined two relations,postgresql accessed both relations
>> sequentially one by one and that too only once.Then how is it joining two
>> relations by accessing only once? Is it storing that accessed relations
>> some where other than main memory/bufferpool(Becos they cant fit into main
>> memory).
>
> What kind of a join is it? If it's a merge join, using indexes, it would only
> have to visit each heap page once.
>
>> So can anybdy tell me is there is ne thing I am missing? Is there any
>> concept like postgresql cache similar to kernel cache otherthan
>> sharedbuffers.If so how can we figure it out.
>
> No. All access to relations (except temporary relations) go through bufmgr
> and the shared memory buffer cache.
>
>> Is there any way by which postgresql is accessing database relations
>> through,other than rotines in bufmgr.c nd freelist.c(I mean any other
>> routines like ReadBuffer,StrategyGet etc.)
>
> No.
>
>

--
N Praveen Kumar
Btech-IV CSE
IIIT,Hyd
AP,India

Imagination is more important than knowledge...
--Albert Einstein