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

[David Jarvis]

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:

"Result  (cost=0.00..21781.18 rows=8090 width=28)"
"  ->  Append  (cost=0.00..21781.18 rows=8090 width=28)"
"        ->  Seq Scan on measurement m  (cost=0.00..28.00 rows=1 width=38)"
"              Filter: ((category_id = 1) AND (station_id = 2043))"
"        ->  Bitmap Heap Scan on measurement_01_001 m  (cost=11.79..1815.67
rows=677 width=28)"
"              Recheck Cond: (station_id = 2043)"
"              Filter: (category_id = 1)"
"              ->  Bitmap Index Scan on measurement_01_001_s_idx
(cost=0.00..11.62 rows=677 width=0)"
"                    Index Cond: (station_id = 2043)"
"        ->  Bitmap Heap Scan on measurement_02_001 m  (cost=14.47..1682.18
rows=627 width=28)"
"              Recheck Cond: (station_id = 2043)"
"              Filter: (category_id = 1)"
"              ->  Bitmap Index Scan on measurement_02_001_s_idx
(cost=0.00..14.32 rows=627 width=0)"
"                    Index Cond: (station_id = 2043)"

2500+ rows in 185 milliseconds.

That is pretty good (I'll need it to be better but for now it works).

Then combined the selection of the station:

SELECT
  m.*
FROM
  climate.measurement m,
  (SELECT
     s.id
   FROM
     climate.station s,
     climate.city c
   WHERE
     c.id = 5182 AND
     s.elevation BETWEEN 0 AND 3000 AND
     6371.009 * SQRT(
       POW(RADIANS(c.latitude_decimal - s.latitude_decimal), 2) +
       (COS(RADIANS(c.latitude_decimal + s.latitude_decimal) / 2) *
        POW(RADIANS(c.longitude_decimal - s.longitude_decimal), 2))
     ) <= 25
   ) t
WHERE
  m.category_id = 1 and
  m.station_id = t.id

The station index is no longer used, resulting in full table scans:

"Hash Join  (cost=1045.52..1341150.09 rows=14556695 width=28)"
"  Hash Cond: (m.station_id = s.id)"
"  ->  Append  (cost=0.00..867011.99 rows=43670085 width=28)"
"        ->  Seq Scan on measurement m  (cost=0.00..25.00 rows=6 width=38)"
"              Filter: (category_id = 1)"
"        ->  Seq Scan on measurement_01_001 m  (cost=0.00..71086.96
rows=3580637 width=28)"
"              Filter: (category_id = 1)"
"        ->  Seq Scan on measurement_02_001 m  (cost=0.00..64877.40
rows=3267872 width=28)"
"              Filter: (category_id = 1)"
"        ->  Seq Scan on measurement_03_001 m  (cost=0.00..71131.44
rows=3582915 width=28)"
"              Filter: (category_id = 1)"

How do I avoid the FTS?

(I know about PostGIS but I can only learn and do so much at once.) ;-)

Here's the station query:

SELECT
  s.id
FROM
  climate.station s,
  climate.city c
WHERE
  c.id = 5182 AND
  s.elevation BETWEEN 0 AND 3000 AND
  6371.009 * SQRT(
    POW(RADIANS(c.latitude_decimal - s.latitude_decimal), 2) +
    (COS(RADIANS(c.latitude_decimal + s.latitude_decimal) / 2) *
    POW(RADIANS(c.longitude_decimal - s.longitude_decimal), 2))
  ) <= 25

And its EXPLAIN:

"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.

Thanks again!
Dave