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.43rows=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 <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:
>> 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
