Following is the output of query i have executed.
praveen=# explain select count(*) from a_9000_0,b_9000_0 where a2=b2;
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate (cost=1924635.42..1924635.43 rows=1 width=0)
-> Merge Join (cost=109515.42..1665435.42 rows=103680000 width=0)
Merge Cond: (a_9000_0.a2 = b_9000_0.b2)
-> Sort (cost=54757.71..55117.71 rows=144000 width=260)
Sort Key: a_9000_0.a2
-> Seq Scan on a_9000_0 (cost=0.00..6979.00 rows=144000
width=260)
-> Sort (cost=54757.71..55117.71 rows=144000 width=260)
Sort Key: b_9000_0.b2
-> Seq Scan on b_9000_0 (cost=0.00..6979.00 rows=144000
width=260)
(9 rows)
On Fri, 8 Sep 2006, Heikki Linnakangas wrote:
Date: Fri, 08 Sep 2006 14:57:57 +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:
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?
Hmm. A hash join, maybe? You should do EXPLAIN on the query to see what it
really does, otherwise we're just guessing.
I traced scanning of relation by editing the functions ReadBuffer() and
BufferAlloc(),StrategyGetBuffer().
That sounds valid.
--
N Praveen Kumar
Btech-IV CSE
IIIT,Hyd
AP,India
Imagination is more important than knowledge...
--Albert Einstein
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq