Re: Optimize date query for large child tables: GiST or GIN?

On Thu, 20 May 2010, David Jarvis wrote:
> I took out the date conditions:
>
> SELECT
> m.*
> FROM
> climate.measurement m
> WHERE
> m.category_id = 1 and
> m.station_id = 2043
>
> This uses the station indexes:

Yes, because there is only one station_id selected. That's exactly what an
index is for.

> Then combined the selection of the station:
> The station index is no longer used, resulting in full table scans:

> "Nested Loop (cost=0.00..994.94 rows=4046 width=4)"
> " Join Filter: ((6371.009::double precision *
> sqrt((pow(radians(((c.latitude_decimal - s.latitude_decimal))::double
> precision), 2::double precision) + (cos((radians(((c.latitude_decimal +
> s.latitude_decimal))::double precision) / 2::double precision)) *
> pow(radians(((c.longitude_decimal - s.longitude_decimal))::double
> precision), 2::double precision))))) <= 25::double precision)"
> " -> Index Scan using city_pkey1 on city c (cost=0.00..6.27 rows=1
> width=16)"
> " Index Cond: (id = 5182)"
> " -> Seq Scan on station s (cost=0.00..321.08 rows=12138 width=20)"
> " Filter: ((s.elevation >= 0) AND (s.elevation <= 3000))"
>
> I get a set of 78 rows returned in very little time.

(An EXPLAIN ANALYSE would be better here). Look at the expected number of
stations returned. It expects 4046 which is a large proportion of the
available stations. It therefore expects to have to touch a large
proportion of the measurement table, therefore it thinks that it will be
fastest to do a seq scan. In actual fact, for 78 stations, the index would
be faster, but for 4046 it wouldn't.

If you will be querying by season quite regularly, had you considered
partitioning by season?

Matthew

--
Geography is going places.