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.


On Fri, 8 Sep 2006, Heikki Linnakangas wrote:

Date: Fri, 08 Sep 2006 14:30:01 +0100
From: Heikki Linnakangas <[EMAIL PROTECTED]>
To: Praveen Kumar N <[EMAIL PROTECTED]>
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.)


 N Praveen Kumar
 Btech-IV CSE

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not

Reply via email to