Improve query performance

[jdemaeyer]

• [ ] Change benchmark suite to use Falcon resources
• [ ] Add benchmarks for current_weather
• [ ] Profile endpoint/processing code
• [ ] Profile database queries

Possible performance improvements:

• [ ] Cache source ID list for location rounded to .01 degrees
• [ ] Store min_date, max_date for each source
• [ ] Limit source ID list to X source IDs per observation type
• [ ] Query only first source ID by default, then query all source IDs on incomplete or missing records

[jdemaeyer]

Before:

Sources by lat/lon:
  100  queries, sequential:           0.93 s
  100  queries, parallel:             0.45 s

Sources by station:
  100  queries, sequential:           0.24 s
  100  queries, parallel:             0.12 s

Sources by source:
  100  queries, sequential:           0.11 s
  100  queries, parallel:             0.07 s

Weather by lat/lon:
  100  one-day queries, sequential:   5.68 s
  100  one-day queries, parallel:     3.43 s
  100 one-week queries, sequential:  44.43 s
  100 one-week queries, parallel:    35.33 s

Weather by lat/lon, today:
  100  one-day queries, sequential:  30.23 s
  100  one-day queries, parallel:    15.76 s

Weather by station:
  100  one-day queries, sequential:   2.78 s
  100  one-day queries, parallel:     1.39 s
  100 one-week queries, sequential:  16.55 s
  100 one-week queries, parallel:     8.55 s

Weather by source:
  100  one-day queries, sequential:   0.75 s
  100  one-day queries, parallel:     0.39 s
  100 one-week queries, sequential:   4.28 s
  100 one-week queries, parallel:     2.31 s

Current weather by lat/lon:
  100  queries, sequential:           1.24 s
  100  queries, parallel:             0.60 s

Current weather by station:
  100  queries, sequential:           0.41 s
  100  queries, parallel:             0.21 s

After:

Sources by lat/lon:
  100  queries, sequential:           1.17 s
  100  queries, parallel:             0.63 s

Sources by station:
  100  queries, sequential:           0.29 s
  100  queries, parallel:             0.13 s

Sources by source:
  100  queries, sequential:           0.10 s
  100  queries, parallel:             0.07 s

Weather by lat/lon:
  100  one-day queries, sequential:   3.54 s
  100  one-day queries, parallel:     1.74 s
  100 one-week queries, sequential:  26.16 s
  100 one-week queries, parallel:    14.74 s

Weather by lat/lon, today:
  100  one-day queries, sequential:  10.58 s
  100  one-day queries, parallel:     5.65 s

Weather by station:
  100  one-day queries, sequential:   1.50 s
  100  one-day queries, parallel:     0.74 s
  100 one-week queries, sequential:   6.21 s
  100 one-week queries, parallel:     3.19 s

Weather by source:
  100  one-day queries, sequential:   0.75 s
  100  one-day queries, parallel:     0.39 s
  100 one-week queries, sequential:   4.26 s
  100 one-week queries, parallel:     2.31 s

Current weather by lat/lon:
  100  queries, sequential:           1.37 s
  100  queries, parallel:             0.72 s

Current weather by station:
  100  queries, sequential:           0.43 s
  100  queries, parallel:             0.24 s

[jdemaeyer]

Then again just fixing the goddamn database index does so much more than any "smart code"...

Sources by lat/lon:                                                 
  100  queries, sequential:           1.10 s
  100  queries, parallel:             0.62 s

Sources by station:
  100  queries, sequential:           0.26 s
  100  queries, parallel:             0.14 s

Sources by source:
  100  queries, sequential:           0.11 s
  100  queries, parallel:             0.07 s

Weather by lat/lon:
  100  one-day queries, sequential:   0.86 s
  100  one-day queries, parallel:     0.57 s
  100 one-week queries, sequential:   2.13 s
  100 one-week queries, parallel:     1.93 s

Weather by lat/lon, today:
  100  one-day queries, sequential:   1.39 s
  100  one-day queries, parallel:     0.81 s

Weather by station:
  100  one-day queries, sequential:   0.87 s
  100  one-day queries, parallel:     0.61 s
  100 one-week queries, sequential:   1.92 s
  100 one-week queries, parallel:     1.14 s

Weather by source:
  100  one-day queries, sequential:   0.26 s
  100  one-day queries, parallel:     0.21 s
  100 one-week queries, sequential:   0.79 s
  100 one-week queries, parallel:     0.73 s

Current weather by lat/lon:
  100  queries, sequential:           1.37 s
  100  queries, parallel:             0.81 s

Current weather by station:
  100  queries, sequential:           0.59 s
  100  queries, parallel:             0.23 s