How can I improve a simple join on a very small table? (postgres)

Question

I have a large table with a foreign key to a smaller table:

CREATE TABLE cars (
  make_model_id INTEGER INDEXED REFERENCES models (make_model_id),
  color TEXT INDEXED,
  year INTEGER INDEXED,
  details TEXT
);

CREATE TABLE models (
  make_model_id SERIAL PRIMARY KEY,
  make TEXT,
  model TEXT
);

cars is hundreds of millions of rows, while models has about 30 rows total. Most queries look like this:

SELECT * FROM cars JOIN make_model USING (make_model_id)
WHERE
  color = 'green' AND
  year = 2015 AND
  make = 'Honda';

Looking at EXPLAIN, the query planner does a nested loop to join the two tables rather than collecting the rows from models and using the index on cars. If I manually run SELECT make_model_id FROM models WHERE make = 'Honda';, that result is instant, then if I use the result in the original query with an IN clause, it shortens the run time by more than half. Is there a way to do this two-step process in a single query?

I've tried moving the query to an in-line clause (make_model_id IN (SELECT make_model_id FROM models...) and as a CTE, but both yield near-identical performance to the original query.

Answer 1

This is the best index

CREATE INDEX ON cars (color, year);

Make sure that you have correct statistics, particularly on the small table:

ANALYZE make_model;
ANALYZE cars;