Re: PostgreSQL caching

> 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