With an INNER JOIN, both tables must be fully checked/matched (check using EXPLAIN ANALYSIS -> HashJoin), so the index cannot be used here.
Sorry, didn't consider the WITH part.Please share the detailed query plan for more info.
The "bikes" subquery uses field "frame_size" in WHERE clause but the field does not have an index...
ADD: Consider whether it might make sense to take a more generalist approach by only having one entity vehicle with the distinction "car", "bike", etc...? ADD: Consider to do more complex "detailed" SELECTs that are unioned (if that is really needed)?
Marian, Lauri's question is clearly about the planner, and not asking about writing the SQL differently, or changing the data model.
Her sample data puts a 1% chance of cars or bikes matching a dealer, so using the indexes that exist should be preferred over a full scan.
She also implies that w/o the WHERE clause in the CTE's union-all query, the outer JOIN-clause would be pushed down (seems to me),
resulting in likely using the indexes. Lauri, you haven't said which version of PostgreSQL. Did you assume the latest v16? My $0.02. --DD