From: Marty Scholes <marty@outputservices.com>
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL caching
Date: Fri, 21 May 2004 14:10:56 -0600
Message-ID: <40AE6250.6070904@outputservices.com> (raw)
Not knowing a whole lot about the internals of Pg, one thing jumped out
at me, that each trip to get data from bv_books took 2.137 ms, which
came to over 4.2 seconds right there.
The problem "seems" to be the 1993 times that the nested loop spins, as
almost all of the time is spent there.
Personally, I am amazed that it takes 3.585 seconds to index scan
i_bookgenres_genre_id. Is that a composite index? Analyzing the
taables may help, as the optimizer appears to mispredict the number of
rows returned.
I would be curious to see how it performs with an "IN" clause, which I
would suspect would go quite a bit fasrer. Try the following:
SELECT bv_books. * ,
vote_avg,
vote_count
FROM bv_bookgenres,
bv_books
WHERE bv_books.book_id IN (
SELECT book_id
FROM bv_genres
WHERE bv_bookgenres.genre_id = 5830
)
AND bv_bookgenres.genre_id = 5830
ORDER BY vote_avg DESC LIMIT 10 OFFSET 0;
In this query, all of the book_id values are pulled at once.
Who knows?
If you get statisctics on this, please post.
Marty
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: marty@outputservices.com
Subject: Re: PostgreSQL caching
In-Reply-To: <40AE6250.6070904@outputservices.com>
* 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