pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Bad performance with hashjoin
2+ messages / 2 participants
[nested] [flat]

* Bad performance with hashjoin
@ 2004-09-11 12:45  Vitaly Belman <vitalyb@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Vitaly Belman @ 2004-09-11 12:45 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Bad performance with hashjoin
@ 2004-09-11 15:28  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Vitaly Belman <vitalyb@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2004-09-11 15:28 UTC (permalink / raw)
  To: Vitaly Belman <vitalyb@gmail.com>; +Cc: pgsql-performance

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

Reduce random_page_cost and/or increase effective_cache_size.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-09-11 15:28 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-11 12:45 Bad performance with hashjoin Vitaly Belman <vitalyb@gmail.com>
2004-09-11 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>

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