David Jarvis <thangalin@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
