> 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
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]