Message-ID: <40B27C92.9000109@outputservices.com>
Date: Mon, 24 May 2004 16:52:02 -0600
From: Marty Scholes <marty@outputservices.com>
User-Agent: Mozilla/5.0 (X11; U; SunOS sun4u; en-US; rv:0.9.9) Gecko/20020517
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL caching
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

 > Hello Marty,
 >
 > MS> Is that a composite index?
 >
 > It is a regular btree index. What is a composite index?

My apologies.  A composite index is one that consists of multiple fields 
(aka multicolumn index).  The reason I ask is that it was spending 
almost half the time just searching bv_bookgenres, which seemed odd.

I may be speaking out of turn since I am not overly familiar with Pg's 
quirks and internals.

A composite index, or any index of a large field, will lower the number 
of index items stored per btree node, thereby lowering the branching 
factor and increasing the tree depth.  On tables with many rows, this 
can result in many more disk accesses for reading the index.  An index 
btree that is 6 levels deep will require at least seven disk accesses (6 
for the index, one for the table row) per row retrieved.

Not knowing the structure of the indexes, it's hard to say too much 
about it.  The fact that a 1993 row select from an indexed table took 
3.5 seconds caused me to take notice.

 > 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:

I believe that.  The code I posted had a nasty join bug.  If my math is 
right, the query was trying to return 1993*1993, or just under 4 million 
rows.

I didn't see the table structure, but I assume that the vote_avg and 
vote_count fields are in bv_bookgenres.  If no fields are actually 
needed from bv_bookgenres, then the query might be constructed in a way 
that only the index would be read, without loading any row data.

I think that you mentioned this was for a web app.  Do you actually have 
a web page that displays 2000 rows of data?

Good luck,
Marty