On Thu, Oct 5, 2023 at 11:35 AM Marian Wendt <marian.we...@yahoo.com> wrote:
> 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
