Optimizing for fully cached data sets

A basic assumption of the query optimizer is that data is not cached in memory, and therefore all access to an index or table might require some disk activity to retrieve. The planner parameters seq_page_cost and random_page_cost being very high relative to cpu_index_tuple_cost reflects this pessimism.

If in fact the data you are reading is expected to be fully cached in memory, it can be appropriate to dramatically lower these parameters in recognition of that fact. In some cases, it might be appropriate to go so far to make index and table lookups appear no more expensive than the CPU cost of looking at a single row:

SHOW cpu_index_tuple_cost;
 cpu_index_tuple_cost 
----------------------
 0.005

SET seq_page_cost=0.005;
SET random_page_cost=0.005;

It's unlikely you want to set values this low in your postgresql.conf for every query, unless your entire database has proven to be quite small compared to system RAM. As you can adjust these...