Materialized view only for covering index

Question

Given the following table car_year of car model's year:

| id | maker |      model | year |
----------------------------------
|  6 |  Audi |    Allroad | 2001 |
| 12 |  Audi |         A8 | 2008 |
| 14 |  Ford |    Mustang | 1996 |
| 15 | Honda |      Civic | 2000 |
| 19 | Honda |    Insight | 2000 |
| 22 |  Ford |       F150 | 2009 |
| 24 | Honda |     Accord | 2000 |
| 28 |  Ford |       F150 | 2007 |
| 34 |  Audi |         S8 | 2002 |
| 48 |  Ford | Expedition | 2011 |
| 62 |  Ford |     Escort | 2004 |
| 81 |  Ford |   Explorer | 2007 |
| 84 |  Ford |     Escape | 2006 |
| 93 | Honda |     Accord | 1995 |

I would like to have a covering index for the "earliest model of a maker". My solution is to create a materialized view:

CREATE MATERIALIZED VIEW earliest AS
    SELECT DISTINCT ON(maker) maker, model
    FROM car_year
    ORDER BY maker, year

And then a covering index over it:

CREATE INDEX earliest_index ON earliest(maker) INCLUDE (model);

It works! But the materialized views is useless (for my usage) because I will only use the the covering index. Am I missing a more elegant solution, or a (Postgre)SQL feature that I don't know about ?

I only need the *earliest* model, not all models for a maker. If I create the index on the base table, `SELECT model FROM car_year WHERE maker = 'ford'` will return 7 rows, when it should only return 1 with "Mustang". — ElBidoule, Sep 17 '20 at 22:30

score 2 · Accepted Answer · answered Sep 18 '20 at 01:55

If I get you right, you are asking for an “index organized table” (or index organized materialized view in that case), that is a table that is really an index. Then you wouldn't have to waste twice the storage, once for the table that you never use, and once for the index, on which you want to perform index-only scans.

The answer is that such “index organized tables” don't exist in PostgreSQL.

Materialized view only for covering index

1 Answers1