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.
Ideally 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.
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.