From: Tom Lane <tgl@sss.pgh.pa.us>
To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] indexes
Date: Mon, 31 May 1999 11:20:06 -0400
Message-ID: <8979.928164006@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.GS4.4.02A.9905311027530.26326-100000@netra.gdansk.sprint.pl>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, rems@gdansk.sprint.pl
Subject: Re: [SQL] indexes
In-Reply-To: <8979.928164006@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox