Re: Random Page Cost and Planner
| От | David Jarvis |
|---|---|
| Тема | Re: Random Page Cost and Planner |
| Дата | |
| Msg-id | AANLkTil_n4-X5OBy4DxDFkwLQ-ywxTIVGmq4W9UNQ_80@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Random Page Cost and Planner (Alexey Klyukin <alexk@commandprompt.com>) |
| Список | pgsql-performance |
Hi, Alexey.
This I have done. I created another table (station_category) that associates stations with when they started to take measurements and when they stopped (based on the data in the measurement table). For example:
This means that station 1 has data for categories 4 through 7. The measurement table returns 3865 rows for station 1 and category 7 (this uses an index and took 7 seconds cold):
The station_category table is basically another index.
Would explicitly sorting the measurement table (273M rows) by station then by date help?
Dave
Is it necessary to get the data as far as 1900 all the time ? Maybe there is a possibility to aggregate results from the past years if they are constant.
station_id; category_id; taken_start; taken_end
1;4;"1984-07-01";"1996-11-30"
1;5;"1984-07-01";"1996-11-30"
1;6;"1984-07-01";"1996-11-10"
1;7;"1984-07-01";"1996-10-31"
1;4;"1984-07-01";"1996-11-30"
1;5;"1984-07-01";"1996-11-30"
1;6;"1984-07-01";"1996-11-10"
1;7;"1984-07-01";"1996-10-31"
This means that station 1 has data for categories 4 through 7. The measurement table returns 3865 rows for station 1 and category 7 (this uses an index and took 7 seconds cold):
station_id; taken; amount
1;"1984-07-01";0.00
1;"1984-07-02";0.00
1;"1984-07-03";0.00
1;"1984-07-04";0.00
1;"1984-07-01";0.00
1;"1984-07-02";0.00
1;"1984-07-03";0.00
1;"1984-07-04";0.00
The station_category table is basically another index.
Would explicitly sorting the measurement table (273M rows) by station then by date help?
Dave
В списке pgsql-performance по дате отправления: