From: | Vitaly Belman <vitalib(at)012(dot)net(dot)il> |
---|---|
To: | Marty Scholes <marty(at)outputservices(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: PostgreSQL caching |
Date: | 2004-05-22 22:22:09 |
Message-ID: | 188896456515.20040523012209@012.net.il |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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=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
From | Date | Subject | |
---|---|---|---|
Next Message | Dan Harris | 2004-05-23 03:30:25 | Re: tuning for AIX 5L with large memory |
Previous Message | Neil Conway | 2004-05-22 02:31:15 | Re: tuning for AIX 5L with large memory |