pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marian Wendt <marian.wendt@yahoo.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Index scan is not pushed down to union all subquery
Date: Thu, 5 Oct 2023 11:33:57 +0200
Message-ID: <3089ff2c-2c50-4503-bd13-80452562756f@yahoo.com> (raw)
In-Reply-To: <18a6d772-aa0d-4710-8d87-8480aa51d1fc@yahoo.com>
References: <CAKWoFMKhbrLFoa6RXip1wHQgcWx+cO7bzD3kU8iWryDgCgVZSw@mail.gmail.com>
	<61de2059-b56b-45f4-98db-a5d9b982fa4b@yahoo.com>
	<18a6d772-aa0d-4710-8d87-8480aa51d1fc@yahoo.com>

>>> Hi,
>>>
>>> Is it intended that indexes are not pushed down to union all
>>> subqueries if even a single select contains a where clause? Is this
>>> just not implemented, is it impossible to implement or am I doing
>>> something wrong?
>>>
>>> The following query does a SeqScan for "bikes" and "cars" tables even
>>> though IndexScan on their column "dealer_name" would be a magnitude
>>> faster. (Schema with sample data at the bottom of this mail)
>>>
>>> WITH
>>>    targets as (
>>>      select 'bike' vehicle, id, dealer_name FROM bikes WHERE frame_size = 52
>>>      union all
>>>      select 'car' vehicle, id, dealer_name 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,315,468)
>>>
>>>
>>> If the WHERE clause from the "bikes" subquery is removed then first
>>> Index Scan on dealers_pk is made and then Bitmap Index Scans on
>>> "bikes" and "cars" table using indexes on "dealer_name" columns.
>>>
>>>
>>> ---------------------------------------------------------------
>>>   -- Available also at:https://www.db-fiddle.com/f/cEXt8HXSaQzsQ2yBDA2Z4H/7
>>> CREATE TABLE dealers AS
>>> SELECT
>>>    id,
>>>    (SELECT string_agg(CHR(65+(random() * 25)::integer), '') FROM
>>> generate_series(1, 4) WHERE id>0) name
>>> FROM generate_series(1, 1000) AS id
>>> ;
>>> ALTER TABLE dealers ADD primary key (id);
>>> CREATE INDEX ON dealers(name);
>>>
>>>
>>> CREATE TABLE bikes AS
>>> SELECT
>>>    generate_series AS id,
>>>    (SELECT name FROM dealers WHERE dealers.id = (SELECT
>>> (random()*1000)::int WHERE generate_series>0)) AS dealer_name,
>>>    (random()*12+50)::int as frame_size
>>> FROM generate_series(1, 100000);
>>> ALTER TABLE bikes ADD primary key (id);
>>> CREATE INDEX ON bikes(dealer_name);
>>>
>>>
>>> CREATE TABLE cars AS
>>> SELECT
>>>    generate_series as id,
>>>    (SELECT name FROM dealers WHERE dealers.id = (SELECT
>>> (random()*1000)::int WHERE generate_series>0)) AS dealer_name,
>>>    (random()*7+14)::int as wheel_size
>>> FROM generate_series(1, 100000);
>>> ALTER TABLE cars ADD primary key (id);
>>> CREATE INDEX ON cars(dealer_name);
>>>
>>>
>>> ANALYZE;
>>>
>>>
>>> --
>>> - Lauri
>>>
>>>
>>
>> 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)?
-- 
regards, marian wendt

view thread (7+ messages)  latest in thread

Message-ID: <3089ff2c-2c50-4503-bd13-80452562756f@yahoo.com>
Permalink:  ../3089ff2c-2c50-4503-bd13-80452562756f@yahoo.com/
Also on:    postgresql.org/message-id/3089ff2c-2c50-4503-bd13-80452562756f@yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: marian.wendt@yahoo.com
  Subject: Re: Index scan is not pushed down to union all subquery
  In-Reply-To: <3089ff2c-2c50-4503-bd13-80452562756f@yahoo.com>
  References: <CAKWoFMKhbrLFoa6RXip1wHQgcWx+cO7bzD3kU8iWryDgCgVZSw@mail.gmail.com> <61de2059-b56b-45f4-98db-a5d9b982fa4b@yahoo.com> <18a6d772-aa0d-4710-8d87-8480aa51d1fc@yahoo.com> <3089ff2c-2c50-4503-bd13-80452562756f@yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox