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]>
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

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?


Reply via email to