Thread: Bad performance with hashjoin

Bad performance with hashjoin

From
Vitaly Belman
Date:
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

Re: Bad performance with hashjoin

From
Tom Lane
Date:
Vitaly Belman <vitalyb@gmail.com> writes:
> What am I to do?

Reduce random_page_cost and/or increase effective_cache_size.

            regards, tom lane