X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C212B329E7B for ; Sat, 11 Sep 2004 13:45:51 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 11189-08 for ; Sat, 11 Sep 2004 12:45:47 +0000 (GMT) Received: from mproxy.gmail.com (rproxy.gmail.com [64.233.170.198]) by svr1.postgresql.org (Postfix) with ESMTP id 4AF91329E6D for ; Sat, 11 Sep 2004 13:45:48 +0100 (BST) Received: by mproxy.gmail.com with SMTP id 79so37375rnk for ; Sat, 11 Sep 2004 05:45:42 -0700 (PDT) Received: by 10.38.14.33 with SMTP id 33mr148753rnn; Sat, 11 Sep 2004 05:45:42 -0700 (PDT) Received: by 10.38.78.36 with HTTP; Sat, 11 Sep 2004 05:45:42 -0700 (PDT) Message-ID: Date: Sat, 11 Sep 2004 15:45:42 +0300 From: Vitaly Belman Reply-To: Vitaly Belman To: Postgresql Performance Subject: Bad performance with hashjoin Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/73 X-Sequence-Number: 8201 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