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,
}
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
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
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
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
