3 ms·
Most databases will allow you to collect statistics on every query run by users, e.g., pg_stat_statements: https://www.postgresql.org/docs/current/pgstatstateme
by ako 3y ago
Most databases will allow you to collect statistics on every query run by users, e.g., pg_stat_statements: https://www.postgresql.org/docs/current/pgstatstatements.html https://www.postgresql.org/docs/current/pgstatstatements.htm...
A relational database really shines when you have all sorts of queries on tables, so it’s hard to optimize your model for every potentially future desired query. Precalculating these, caching the results in separate tables is not very efficient, and requires you to predetermine every query you want to answer.
Indexing in this situation is often the better alternative.
- szundi 3y agoLots of apps however do the same N kind of queries, but the data under them can change over time without anyone noticing
- ako 3y agoSo? Just make sure you regularly refresh the statistics...
- sgarland 3y agoIdeally yes, but sometimes your tables get so monstrously large that the auto-analyze is sampling thousandths of a percent of rows, and fiddling with sampling targets can throw off seemingly unconnected queries. Planners are mysterious black boxes, which we can at best influence.
- ako 3y agoYes, a bit like machine learning models.