David Jarvis <thangalin@gmail.com> wrote:
>> It sounds as though the active portion of your database is pretty
>> much cached in RAM. True?
> I would not have thought so; there are seven tables, each with 39
> to 43 million rows
> The machine has 4GB of RAM
In that case, modifying seq_page_cost or setting random_page_cost
below something in the range of 1.5 to 2 is probably not going to be
a good choice for the mix as a whole.
> effective_cache_size = 256MB
This should probably be set to something on the order of 3GB. This
will help the optimizer make more intelligent choices about when use
of the index will be a win.
>> It would tend to be better than random access to 43 million rows,
>> at least if you need to go to disk for many of them.
>
> I thought that the index would take care of this?
When the index can limit the number of rows to a fraction of the 43
million rows, using it is a win. The trick is to accurately model
the relative costs of different aspects of running the query, so
that when the various plans are compared, the one which looks the
cheapest actually *is*. Attempting to force any particular plan
through other means is risky.
> I will be trying various other indexes. I've noticed now that
> sometimes the results are very quick and sometimes very slow. For
> the query I posted, it would be great to know what would be the
> best indexes to use. I have a suspicion that that's going to
> require trial and many errors.
Yeah, there's no substitute for testing your actual software against
the actual data. Be careful, though -- as previously mentioned
caching can easily distort results, particularly when you run the
same query, all by itself (with no competing queries) multiple
times. You'll get your best information if you can simulate a
more-or-less realistic load, and try that with various settings and
indexes. The cache turnover and resource contention involved in
production can influence performance, and are hard to estimate any
other way.
-Kevin