Re: Index scan is not pushed down to union all subquery

On Thu, Oct 5, 2023 at 11:35 AM Marian Wendt <marian(dot)wendt(at)yahoo(dot)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