Increase the speed optimizations for computing direction on covidcast signals

[krivard]

One of the recent direction runs took 90 minutes to complete, which seems excessive.

I suspect we are doing something that was easy to implement but not terribly scalable, like updating the direction for all dates and all geo_ids when only a small number of them were invalidated, and perhaps also using a separate query for each time series with updates (there were 72546 of those in the 90-minute run).

If it turns out the database code isn’t the issue, we might have to think about moving the direction computation outside of epidata and into the individual indicators.

[krivard]

@AlSaeed proposed the following:

1. The primary id of each row should be also retrieved when retrieving the time series.
2. 4 lists of row ids should be computed (across timeseries). These are the rows that will change their direction value to [-1,0,1,NULL].
3. 4 batched queries to update the direction field only, based on the ids of the lists above.
4. When retrieving the stale timeseries we should store them in temporary table.
5. Updating timestamp2 can be done in 1 query, using the temporary table from [4.].
6. For the retrieval part, if we expect the data of all timeseries to fit in memory simultaneously, we can use the temporary table from [4.] to retrieve the entire data and use pandas to separate them into independent series.

This will work, with some modifications to support the upcoming shift to include issue dates. I've consulted with @jacobbien, and he suggests that since direction is relatively complex to compute (it's the slope of a line fit to all values of the previous 7 days, thresholded based on the variance of historical data), that it's really a computed product, not raw data. Therefore, updating the direction column should be done in-place without creating a new issue for the affected timepoint.

As an example, consider the following input to the direction updater:

geo value	value	time value	issue	direction	direction timestamp
ca	4.1	20200601	20200601	0	stale
ca	4.0	20200602	20200602	0	stale
ca	4.1	20200603	20200603	0	stale
ca	4.2	20200604	20200604	0	stale
ca	5.0	20200605	20200605	null	stale

The proposed solution is the following:

geo value	value	time value	issue	direction	direction timestamp
ca	4.1	20200601	20200601	1	fresh
ca	4.0	20200602	20200602	1	fresh
ca	4.1	20200603	20200603	1	fresh
ca	4.2	20200604	20200604	1	fresh
ca	5.0	20200605	20200605	1	fresh

The alternative is the following:

geo value	value	time value	issue	direction	direction timestamp
ca	4.1	20200601	20200601	0	stale
ca	4.1	20200601	20200605	1	fresh
ca	4.0	20200602	20200602	0	stale
ca	4.0	20200602	20200605	1	fresh
ca	4.1	20200603	20200603	0	stale
ca	4.1	20200603	20200605	1	fresh
ca	4.2	20200604	20200604	0	stale
ca	4.2	20200604	20200605	1	fresh
ca	5.0	20200605	20200605	1	fresh

[krivard]

@melange396, barring unforeseen complications we're expecting a PR for query optimizations on this tomorrow -- if database calls turn out to be the top contributor in your profiling efforts, ignore them for now.

[krivard]

First pass of implementation is in #133