On Thu, Oct 5, 2023 at 4:25 PM Lauri Kajan <lauri.kajan@xxxxxxxxx> 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)
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
