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
