Postgres Pro
Downloads
Home   >   mailing lists

Re: Index scan is not pushed down to union all subquery - Mailing list pgsql-general

From Lauri Kajan
Subject Re: Index scan is not pushed down to union all subquery
Date
Msg-id CAKWoFMLhGX4owfisM4_zY40iaJyo5vbOffbJzRq=yHTTBkEGkA@mail.gmail.com
Whole thread Raw
In response to Re: Index scan is not pushed down to union all subquery  (Lauri Kajan <lauri.kajan@gmail.com>)
List pgsql-general
Tree view

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

pgsql-general by date:

Previous
From: Dow Drake
Date:
Subject: Re: Multiple inserts with two levels of foreign keys
Next
From: Michał Kłeczek
Date:
Subject: GIST index and ORDER BY