Re: PostgreSQL caching - Mailing list pgsql-performance

Hello Marty,

MS> Is that a composite index?

It is a regular btree index. What is a composite index?

MS> Analyzing the taables may help, as the optimizer appears to
MS> mispredict the number of rows returned.

I'll try analyzing, but I highly doubt that it would help. I analyzed
once already and haven't changed the data since.

MS> I would be curious to see how it performs with an "IN" clause,
MS> which I would suspect would go quite a bit fasrer.

Actually it reached 20s before I canceled it... Here's the explain:

QUERY PLAN
Limit  (cost=3561.85..3561.88 rows=10 width=76)
  ->  Sort  (cost=3561.85..3562.39 rows=214 width=76)
        Sort Key: bv_books.vote_avg
        ->  Nested Loop  (cost=1760.75..3553.57 rows=214 width=76)
              ->  Index Scan using i_bookgenres_genre_id on bv_bookgenres  (cost=0.00..1681.54 rows=214 width=0)
                    Index Cond: (genre_id = 5830)
              ->  Materialize  (cost=1760.75..1761.01 rows=26 width=76)
                    ->  Nested Loop  (cost=1682.07..1760.75 rows=26 width=76)
                          ->  HashAggregate  (cost=1682.07..1682.07 rows=26 width=4)
                                ->  Index Scan using i_bookgenres_genre_id on bv_bookgenres  (cost=0.00..1681.54
rows=214width=4) 
                                      Index Cond: (genre_id = 5830)
                          ->  Index Scan using bv_books_pkey on bv_books  (cost=0.00..3.01 rows=1 width=76)
                                Index Cond: (bv_books.book_id = "outer".book_id)


Thank you for your try.

Regards,
Vitaly Belman

 ICQ: 1912453
 AIM: VitalyB1984
 MSN: tmdagent@hotmail.com
 Yahoo!: VitalyBe

Friday, May 21, 2004, 11:10:56 PM, you wrote:

MS> Not knowing a whole lot about the internals of Pg, one thing jumped out
MS> at me, that each trip to get data from bv_books took 2.137 ms, which
MS> came to over 4.2 seconds right there.

MS> The problem "seems" to be the 1993 times that the nested loop spins, as
MS> almost all of the time is spent there.

MS> Personally, I am amazed that it takes 3.585 seconds to index scan
MS> i_bookgenres_genre_id.  Is that a composite index?  Analyzing the
MS> taables may help, as the optimizer appears to mispredict the number of
MS> rows returned.

MS> I would be curious to see how it performs with an "IN" clause, which I
MS> would suspect would go quite a bit fasrer.  Try the following:

MS> SELECT     bv_books. * ,
MS>             vote_avg,
MS>             vote_count
MS> FROM       bv_bookgenres,
MS>             bv_books
MS> WHERE      bv_books.book_id IN (
MS>                SELECT book_id
MS>                FROM bv_genres
MS>                WHERE bv_bookgenres.genre_id = 5830
MS>                )
MS> AND bv_bookgenres.genre_id = 5830
MS> ORDER BY   vote_avg DESC LIMIT 10 OFFSET 0;

MS> In this query, all of the book_id values are pulled at once.

MS> Who knows?

MS> If you get statisctics on this, please post.

MS> Marty


MS> ---------------------------(end of
MS> broadcast)---------------------------
MS> TIP 4: Don't 'kill -9' the postmaster