Re: PostgreSQL caching

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=214 width=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(at)hotmail(dot)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