Re: postgresql shared buffers - Mailing list pgsql-hackers

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@enterprisedb.com>
> To: Praveen Kumar N <praveen_n@students.iiit.net>
> Cc: pgsql-hackers@postgresql.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