pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBad performance with hashjoin
2+ messages / 2 participants
[nested] [flat]
* Bad performance with hashjoin
@ 2004-09-11 12:45 Vitaly Belman <vitalyb@gmail.com>
2004-09-11 15:28 ` Re: Bad performance with hashjoin Tom Lane <tgl@sss.pgh.pa.us>
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 12:45 Bad performance with hashjoin Vitaly Belman <vitalyb@gmail.com>
@ 2004-09-11 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
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