home-assistant / home-assistant / 27659

94%

push

travis-ci

<a href="https://github.com/home-assistant/home-assistant/commit/6e1785173">History query and schema optimizations for huge performance boost (#8748)

* Add DEBUG-level log for db row to native object conversion

This is now the bottleneck (by a large margin) for big history queries, so I&#39;m leaving this log feature in to help diagnose users with a slow history page

* Rewrite of the &quot;first synthetic datapoint&quot; query for multiple entities

The old method was written in a manner that prevented an index from being used in the inner-most GROUP BY statement, causing massive performance issues especially when querying for a large time period.

The new query does have one material change that will cause it to return different results than before: instead of using max(state_id) to get the latest entry, we now get the max(last_updated). This is more appropriate (primary key should not be assumed to be in order of event firing) and allows an index to be used on the inner-most query. I added another JOIN layer to account for cases where there are two entries on the exact same `last_created` for a given entity. In this case we do use `state_id` as a tiebreaker.

For performance reasons the domain filters were also moved to the outermost query, as it&#39;s way more efficient to do it there than on the innermost query as before (due to indexing with GROUP BY problems)

The result is a query that only needs to do a filesort on the final result set, which will only be as many rows as there are entities.

* Remove the ORDER BY entity_id when fetching states, and add logging

Having this ORDER BY in the query prevents it from using an index due to the range filter, so it has been removed.

We already do a `groupby` in the `states_to_json` method which accomplishes exactly what the ORDER BY in the query was trying to do anyway, so this change causes no functional difference.

Also added DEBUG-level logging... (continued)

64 of 64 new or added lines in 4 files covered. (100.0%)

23900 of 25491 relevant lines covered (93.76%)

2.81 hits per line