From: Kevin Grittner <kgrittn@mail.com>
To: Huan Ruan <huan.ruan.it@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: hash join vs nested loop join
Date: Wed, 19 Dec 2012 08:16:37 -0500
Message-ID: <20121219131637.14740@gmx.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
"Huan Ruan" <huan.ruan.it@gmail.com> wrote:
> explain (analyze, buffers)
> SELECT
> *
> FROM IM_Match_Table smalltable
> inner join invtran bigtable on bigtable.invtranref = smalltable.invtranref
Well, one table or the other will need to be read in full, and you
would normally want that one to be the small table. When there is
no ORDER BY clause, the fastest way to do that will normally be a
seqscan. So that part of the query is as it should be. The only
question is whether the access to the big table is using the
fastest technique.
If you want to see what the planner's second choice would have
been, you could run:
SET enable_indexscan = off;
on a connection and try the explain again. If you don't like that
one, you might be able to disable another node type and see what
you get. If one of the other alternatives is faster, that would
suggest that adjustments are needed to the costing factors;
otherwise, it just takes that long to read hundreds of thousands of
rows in one table and look for related data for each of them in
another table.
> "Nested Loop (cost=0.00..341698.92 rows=48261 width=171) (actual
> time=0.042..567.980 rows=48257 loops=1)"
Frankly, at 12 microseconds per matched pair of rows, I think
you're doing OK.
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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-performance@postgresql.org
Cc: kgrittn@mail.com, huan.ruan.it@gmail.com
Subject: Re: hash join vs nested loop join
In-Reply-To: <20121219131637.14740@gmx.com>
* 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