> The computed cost of using the index was a factor of 10 higher which I
presume is why the query planner wasn't
> using the index, but it ran in half the time
Have you tried playing with the random_page_cost parameter? The default is
4. Try:
set random_page_cost = 1;
in psql to alter it for the current session (you can change this in
postgresql.conf too). This will make index usage more attractive by reducing
the computed cost. This is the simple way of looking at it anyway.
On my system I tested a 'typical' query exercising some joins on large
tables which didn't use an index, but I thought maybe it would perform
better if it did. I determined that a random_page_cost of 1.8 would cause
indexes to be used, but in this case the *actual* performance didn't improve
very much. I took this to mean that a random_page_cost of around 1.8/1.9
represents a rough balance point on my development server (one slow IDE
disk, and a big database).
As well as the other things mentioned by Tom, perhaps you should be looking
for the "correct" setting of random_page_cost for your system. It may be
appropriate to alter it globally using postgresql.conf, and for specific
situations such as you mentioned.
HTH
Iain