Hi, Kevin. Thanks for the response.
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 as: CREATE TABLE climate.measurement ( id bigserial NOT NULL, taken date NOT NULL, station_id integer NOT NULL, amount numeric(8,2) NOT NULL, flag character varying(1) NOT NULL DEFAULT ' '::character varying, category_id smallint NOT NULL, } The machine has 4GB of RAM, donated to PG as follows: *shared_buffers = 1GB temp_buffers = 32MB work_mem = 32MB maintenance_work_mem = 64MB effective_cache_size = 256MB * Everything else is at its default value. The kernel: $ cat /proc/sys/kernel/shmmax 2147483648 Two postgres processes are enjoying the (virtual) space: 2619 postgres 20 0 *1126m* 524m 520m S 0 13.2 0:09.41 postgres 2668 postgres 20 0 *1124m* 302m 298m S 0 7.6 0:04.35 postgres can make such plans look more attractive by cutting both > random_page_cost and seq_page_cost. Some highly cached loads > perform well with these set to equal values on the order of 0.1 to > 0.001. > I tried this: no improvement. 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? The index has been set to the unique key of: station_id, taken, and category_id (the filter for child tables). Each time I scan for data, I always provide the station identifier and its date range. The date range is obtained from another table (given the same station_id). 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. Dave
