Re: postgresql shared buffers

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