Optimizing PostgreSQL Queries With Loose Index Scans
Optimizing PostgreSQL queries: Use loose index-only scans for efficient retrieval of recent records, but weigh the trade-offs with increased indexing overhead.
Use-case: we have a set of meters (e.g. gas, electricity, etc) that regularly record readings. Our task is to retrieve the latest reading of every meter (or more generically phrased: retrieving the latest row per group) using SQL. SQL is a declarative language. Therefore, we achieve goal using it by expressing only what we want in the form of queries, instead of providing precise instructions about how to retrieve data. Then the database component called the planner will determine what's the best way to do that based on several factors such as table statistics. However, as we shall see, going...