Re: Random Page Cost and Planner

Hi,

sc.taken_end <= '1996-12-31'::date AND
> m.taken BETWEEN sc.taken_start AND sc.taken_end AND
>
> category of data at a certain time. But I'm afraid this makes the planning
> much more difficult, as the select from measurements depend on the data
> returned by other parts of the query (rows from category).
>

Right. Users can select 1900 - 2009. Station data hardly ever spans that
range.

The *station_category* is used to create a unique key into the measurement
data for every station: station_id, category_id, and taken_start. The
measurement data should be contiguous until taken_end.

I thought that that combination would be a pointer to the exact spot in the
measurement table where the data starts, which should be ridiculously fast
to find.

See this http://explain.depesz.com/s/H1 and this
> http://explain.depesz.com/s/GGx
>

I was getting some red lines when I looked at a different plan. It's a great
site.

How many rows does the query return without the group by clause? About
> 140000 in both cases, right?
>

SELECT
*
FROM
climate.measurement m
WHERE
m.station_id = 5148 AND
m.taken BETWEEN '1900-08-01'::date AND '2009-12-31'::date AND
m.category_id = 1

5397 rows (10 seconds cold; 0.5 seconds hot); estimated too high by 2275
rows?

http://explain.depesz.com/s/uq

OK, I haven't noticed the table is already partitioned by category_id and
> I didn't mean to partition by (taken, category_id) - that would produce a
> lot of partitions. Yes, that might cause problems related to number of
> files, but that's rather a filesystem related issue.
>

Constrained as:

CONSTRAINT measurement_013_category_id_ck CHECK (category_id = 7)

> I'd expect rather issues related to RULEs or triggers (not sure which of
> them you use to redirect the data into partitions). But when partitioning
>

I created seven child tables of measurement. Each of these has a constraint
by category_id. This makes it extremely fast to select the correct
partition.

> I'm not sure what you mean by 'year wrapping issue' but I think it might
> work quite well - right not the problem is PostgreSQL decides to scan the
> whole partition (all data for a given category_id).
>

I'll give it another try. :-)

*Use Case #1*
User selects: Mar 22 to Dec 22
User selects: 1900 to 2009

Result: Query should average *9 months* of climate data per year between Mar
22 and Dec 22 of Year.

*Use Case #2*
User selects: Dec 22 to Mar 22
User selects: 1900 to 2009

Result: Query should average *3 months* of climate data per year between Dec
22 of Year and Mar 22 of Year+1.

So if a user selects 1950 to *1960*:

- first case should average between 1950 and *1960*; and
- second case should average between 1950 and *1961*.

Dave