Re: Random Page Cost and Planner
| От | David Jarvis |
|---|---|
| Тема | Re: Random Page Cost and Planner |
| Дата | |
| Msg-id | AANLkTin-j4SACaMeJCH4r5ekEWrtuiFqU56gVM1_lIyV@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Random Page Cost and Planner ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
| Ответы |
Re: Random Page Cost and Planner
Re: Random Page Cost and Planner Re: Random Page Cost and Planner |
| Список | pgsql-performance |
Hi, Kevin.
Thanks for the response.
I would not have thought so; there are seven tables, each with 39 to 43 million rows as:
The machine has 4GB of RAM, donated to PG as follows:
Everything else is at its default value. The kernel:
Two postgres processes are enjoying the (virtual) space:
I tried this: no improvement.
I thought that the index would take care of this? The index has been set to the unique key of:
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
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
В списке pgsql-performance по дате отправления: