Re: Random Page Cost and Planner

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