DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=gvm9xMt0ystHE3Qo7Dymtb10GhSz3c6PIKQHqf8rb/0VCfWT+47cUjC3KRZQ5Gw9t6aSUXPGgWSZIUd8C2R7/Z3rmDQSnjj15EMKwWHdTN9PG7fRitmDiJE72p+dk+S7CD5HMg1prsWaJy0JM2IN7GbBF7XkkfrKr/uhPsZDcCs=
Message-ID: <130df19305091110125a2d174f@mail.gmail.com>
Date: Sun, 11 Sep 2005 20:12:58 +0300
From: Ksenia Marasanova <ksenia.marasanova@gmail.com>
Reply-To: ksenia.marasanova@gmail.com
To: pgsql-performance@postgresql.org
Subject: LEFT JOIN optimization
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi list,

I don't have much experience with Postgres optimization, somehow I was
happily avoiding anything more difficult than simple select statement,
and it was working all right.

Now LEFT JOIN must be used, and I am not happy with the performance:
It takes about 5 seconds to run very simple LEFT JOIN query on a table
"user_" with ~ 13.000 records left joined to table "church" with ~ 300
records on Powerbook PPC 1.67 GHz with 1.5 GB ram.
Is it normal?

Some details:

test=3D# explain select * from user_ left join church on user_.church_id
=3D church.id;
                             QUERY PLAN                             =20
---------------------------------------------------------------------
 Hash Left Join  (cost=3D6.44..7626.69 rows=3D12763 width=3D325)
   Hash Cond: ("outer".church_id =3D "inner".id)
   ->  Seq Scan on user_  (cost=3D0.00..7430.63 rows=3D12763 width=3D245)
   ->  Hash  (cost=3D5.75..5.75 rows=3D275 width=3D80)
         ->  Seq Scan on church  (cost=3D0.00..5.75 rows=3D275 width=3D80)
(5 rows)