From: Vitaly Belman <vitalib@012.net.il>
To: Marty Scholes <marty@outputservices.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL caching
Date: Sun, 23 May 2004 01:22:09 +0300
Message-ID: <188896456515.20040523012209@012.net.il> (raw)
In-Reply-To: <40AE6250.6070904@outputservices.com>
References: <40AE6250.6070904@outputservices.com>
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@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: vitalib@012.net.il, marty@outputservices.com
Subject: Re: PostgreSQL caching
In-Reply-To: <188896456515.20040523012209@012.net.il>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox