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