Stale table statistics can result in extremely slow queries

Problem/Motivation

Tested this on a site. It was causing serious performance problems on a node with ~20k revisions. The slow queries that where showing up didn't make sense either b/c they should have been the optimized case.

After discussing with Alex on slack, and confirming the poor performance went away when the database was copied to a different server(by way of a database dump) we decided this was likely an issue with the storage causing it to choose a bad subquery logic.

After hours I ran the following. A warning for those that follow, after hours was a good instinct. The queries ran immediately but then took down the site as all other queries touching those tables where queued until it finished running background tasks on the table.

ANALYZE TABLE node_field_data PERSISTENT FOR ALL;
ANALYZE TABLE node_revision PERSISTENT FOR ALL;

This seems to have fixed the problem and the slow node that was timing out now loads immediately and the query the slow query returned instantly.

See #3555720-38: Latest revision subquery optimisation →

Proposed resolution

It would be great to warn people if the statistics get out of date. We need to investigate if we can determine this - I'm not sure a regular DB user will have permissions to run the ANALYZE TABLE query. Plus we need to check all the entity tables. It might be worth generalising to any non cache table.

Remaining tasks

User interface changes

Introduced terminology

API changes

Data model changes

Release notes snippet