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

On Thu, Oct 5, 2023 at 4:25 PM Lauri Kajan <lauri(dot)kajan(at)gmail(dot)com> wrote:

> In my dreams the plan would be something like this:
> Nested Loop
> -> Index Scan using dealers_pkey on dealers
> Index Cond: (id = ANY ('{54,12,456,315,468}'::integer[]))
> -> Append
> -> Bitmap Heap Scan on bikes
> Recheck Cond: (dealer_name = dealers.name)
> Filter: (frame_size = 52)
> Rows Removed by Filter: 91
> -> Bitmap Index Scan on bikes_dealer_name_idx
> Index Cond: (dealer_name = dealers.name)
> -> Bitmap Heap Scan on cars
> Recheck Cond: (dealer_name = dealers.name)
> -> Bitmap Index Scan on cars_dealer_name_idx
> Index Cond: (dealer_name = dealers.name)
>

OK, I'm getting pretty close.
With the following query where I select the filtered column in the union
all and add the where clause to the top level query I get exactly the query
plan I want.

EXPLAIN (ANALYZE)
WITH
targets as (
select 'bike' vehicle, id, dealer_name, frame_size as filter FROM bikes
union all
select 'car' vehicle, id, dealer_name, null as filter FROM cars
-- In the real use case I have here dozens of tables
)
SELECT
dealers.name dealer,
targets.vehicle,
targets.id
FROM
dealers
JOIN targets
ON dealers.name = targets.dealer_name
WHERE
dealers.id in (54,12,456,887,468)
and (filter is null or filter = 52)

But! This is not quite usable since it is tedious to write the query when
there are filters in multiple tables and all different columns must be
added to all the subqueries.

Regardless of that this kind of proves that the desired plan is possible to
run with Postgres. So maybe this is just a missing feature in the Optimizer.

-Lauri