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 <[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.)
No.
--
N Praveen Kumar
Btech-IV CSE
IIIT,Hyd
AP,India
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
match
