Madison Kelly <linux@alteeve.com> writes:
> So the index obiously provides a major performance boost! I just need to
> figure out how to tell the planner how to use it...
Be careful extrapolating too much from a single query in a single context.
Notably you might want to test the same query after not touching this table
for a little while. The index is probably benefiting disproportionately from
having you repeatedly running this one query and having the entire table in
cache.
That said, you should look at lowering random_page_cost. The default is 4 but
if this query is representative of your system's performance then much of your
database is in cache and the effective value will be closer to 1. Try 2 or
even 1.5 or 1.2.
But like I said, test other queries and test under more representative
conditions other than repeating a single query over and over.
--
greg