Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6])
	by hub.org (8.9.3/8.9.3) with ESMTP id LAA02965
	for <pgsql-sql@postgreSQL.org>; Mon, 31 May 1999 11:21:31 -0400 (EDT)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id LAA08981;
	Mon, 31 May 1999 11:20:07 -0400 (EDT)
To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] indexes 
In-reply-to: Your message of Mon, 31 May 1999 10:42:02 +0200 (MET DST)
	<Pine.GS4.4.02A.9905311027530.26326-100000@netra.gdansk.sprint.pl>
Date: Mon, 31 May 1999 11:20:06 -0400
Message-ID: <8979.928164006@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Remigiusz Sokolowski <rems@gdansk.sprint.pl> writes:
> NOTICE:  QUERY PLAN:
> Unique  (cost=77.02 size=0 width=0)
>   ->  Sort  (cost=77.02 size=0 width=0)
>         ->  Nested Loop  (cost=77.02 size=1 width=28)
>               ->  Nested Loop  (cost=74.97 size=1 width=12)
>                     ->  Seq Scan on b1  (cost=72.97 size=1 width=8)
>                     ->  Index Scan on e2  (cost=2.00 size=1 width=4)
>               ->  Index Scan on e1  (cost=2.05 size=1304 width=16)
> 

I think the real problem here is that the optimizer thinks your tables
are small (notice the size=1 estimates in the inner loop).  Have you
done a VACUUM lately?  You need that to update the statistics that the
optimizer uses.

Unless you are dealing with very small tables, you don't want to see
nested-loop joins (that means scanning the lower table once for each
tuple in the upper table!).  You want to see merge joins or hash joins.

Vadim's suggestion of a better-adapted index was a good one, but I
wonder whether the speedup you saw wasn't just a side effect from
CREATE INDEX having updated the optimizer's stats, so that it stopped
using nested loops...

			regards, tom lane