pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Vitaly Belman <vitalyb@gmail.com>
To: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Bad performance with hashjoin
Date: Sat, 11 Sep 2004 15:45:42 +0300
Message-ID: <fa96e3c604091105452e0acd8@mail.gmail.com> (raw)

Here's the query:

---------------------------------------------------------------------------
SELECT * FROM bv_reviews r, bv_votes v 
WHERE r.vote_id = v.vote_id 
AND v.book_id = 113
---------------------------------------------------------------------------

bv_votes has around 7000 rows with the given book_id and bv_reviews
has 10 reviews. Thus the resulting table consists of only 10 rows.

That's the regular EXPLAIN of the query:

---------------------------------------------------------------------------
QUERY PLAN
Hash Join  (cost=169.36..49635.37 rows=2117 width=897) (actual
time=13533.550..15107.987 rows=10 loops=1)
  Hash Cond: ("outer".vote_id = "inner".vote_id)
  ->  Seq Scan on bv_reviews r  (cost=0.00..45477.42 rows=396742
width=881) (actual time=12.020..13305.055 rows=396742 loops=1)
  ->  Hash  (cost=151.96..151.96 rows=6960 width=16) (actual
time=24.673..24.673 rows=0 loops=1)
        ->  Index Scan using i_votes_book_id on bv_votes v 
(cost=0.00..151.96 rows=6960 width=16) (actual time=0.035..14.970
rows=7828 loops=1)
              Index Cond: (book_id = 113)
Total runtime: 15109.126 ms
---------------------------------------------------------------------------

And here is what happens when I turn the hashjoin to off:

---------------------------------------------------------------------------
QUERY PLAN
Nested Loop  (cost=0.00..53799.79 rows=2117 width=897) (actual
time=4.260..79.721 rows=10 loops=1)
  ->  Index Scan using i_votes_book_id on bv_votes v 
(cost=0.00..151.96 rows=6960 width=16) (actual time=0.071..14.100
rows=7828 loops=1)
        Index Cond: (book_id = 113)
  ->  Index Scan using i_bv_reviews_vote_id on bv_reviews r 
(cost=0.00..7.70 rows=1 width=881) (actual time=0.007..0.007 rows=0
loops=7828)
        Index Cond: (r.vote_id = "outer".vote_id)
Total runtime: 79.830 ms
---------------------------------------------------------------------------

What am I to do? Are there hints (like in Oracle) in PostgreSQL to
force it to use the i_bv_reviews_vote_id index instead of doing a
seq.scan? Or is something wrong with my Postgresql settings?

-- 
 ICQ: 1912453
 AIM: VitalyB1984
 MSN: tmdagent@hotmail.com
 Yahoo!: VitalyBe



view thread (2+ messages)  latest in thread

Message-ID: <fa96e3c604091105452e0acd8@mail.gmail.com>
Permalink:  ../fa96e3c604091105452e0acd8@mail.gmail.com/
Also on:    postgresql.org/message-id/fa96e3c604091105452e0acd8@mail.gmail.com

 · 

reply

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: vitalyb@gmail.com
  Subject: Re: Bad performance with hashjoin
  In-Reply-To: <fa96e3c604091105452e0acd8@mail.gmail.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