Re: trouble with (lack of) indexing

On Fri, 10 May 2002, Søren Boll Overgaard wrote:

> > What are your settings in the postgresql.conf for cpu_tuple_cost and such?
>
> On both databases:
> NOTICE:  cpu_tuple_cost is 0.01
> NOTICE:  cpu_index_tuple_cost is 0.001

Here's a quick and dirty explanation of what these settings mean to the
query planner, as listed in src/backend/optimizer/path/costsize.c:

random_page_cost        Cost of a non-sequential page fetch
cpu_tuple_cost          Cost of typical CPU time to process a tuple
cpu_index_tuple_cost    Cost of typical CPU time to process an index tuple

Since the initial numbers are just SWAGs (scientific wild ass guesses) it
would behoove you to figure out where the break points really are in your
setup.

You can change the setting of enable_seqscan and enable_indexscan to force
your planner to ignore one or the other, and time the output.  It's pretty
easy to do a quick timing test like so:

time psql -c 'set enable_seqscan=off;select * from tellers' >/dev/null

When you find a query that is just a little faster with seq scan than
index or vice versa, then you can tune the three listed parameters above
so that the planner just barely makes the right choices for your data.

I've found that lower cpu_index_tuple_cost from the default 0.001 to
0.0005 or so made a big difference in my database, and setting
random_page_cost = 1.5 or so was a good help too.  Your mileage will
certainly vary...