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.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 <heikki(at)enterprisedb(dot)com>
> To: Praveen Kumar N <praveen_n(at)students(dot)iiit(dot)net>
> Cc: pgsql-hackers(at)postgresql(dot)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