Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qoKWs-000tgz-60 for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 09:20:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qoKWq-00B3nF-78 for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 09:20:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qoKWp-00B3mk-Oa for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 09:20:20 +0000 Received: from sonic305-19.consmr.mail.ir2.yahoo.com ([77.238.177.81]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qoKWn-0007j7-Ja for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 09:20:19 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1696497615; bh=Cx/p+p1S4WwRa4LBhfFGoCC5gXrZy9S2Fyzl/QTZG/w=; h=Date:Subject:To:References:From:In-Reply-To:From:Subject:Reply-To; b=O2cv5mWPp7mMK0BacO5idsAnIdm3yTVf5T0k697XdvG0SfqymfRxEORb7w2VDYqDhClktFYbyfUKvW+wE0C6pV4CCZaMp6hsIaA5prO80JvCab63qoI0BLgaISfxM41RotTpjqUaY7lv0G3QGjdzCa8ghkaWgLYy96JAkuErlRIxJYE4/F6nIMi1fylRiirPEk0arlGs7WVs1NfGxakhBABEoa6ubmCLIsmsYsiReui/ZJIsu47ip2y7/Qv7Fm7Jq4J2u9pYJeXK3ZDCl7L7wemy6X5cba5JCpL+urS+8KNBS5r0uXeNBgYqG4PxXrHyK7GSwyhEE9NtjLE9b96Ukg== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1696497615; bh=4NgQjc/NxqUFtv2eknh4otA+0gky+dY21GycVexz2+E=; h=X-Sonic-MF:Date:Subject:To:From:From:Subject; b=uNaQvwlaTceM4L4y0ofnXwknfY/TDkglSfSw/ivV78GNmXs8QX3UI2d0dD/XIkQMCHiol7/HmANtjRTR/fJmn5iwfqmaf5kvYvB1D8QiuN/27EM8edr5jnWjoJGfDCNtiqFzPbMBkqi8dNgX1QCbP3/xjaHEvYSmN30W/MzrYiZpzu0xUZ/52KVS5te94riawb9DVEwhCqwYdtLxu7IJQuixQ//ULmDL89nEFND6SmKl/5Ye2DlzhT7p2DI4A1apVZLsdrKHeH2g58o1ZcWz0+FhdGV6uY60rO8Kw1y0HTZPzqd0jQjHpOVttcBbUS9YDh7mlcXPOYbnTgAuz1lHpg== X-YMail-OSG: A3KDDx4VM1kfpS6qwPb2EesjKw054JToGMBVuwJEe7lnHJ.s8bKcVo4mw_JoLDX vV.W.0jI2JtDqKeWl9kpxTyAWP5M58DvwO6mc2h2y2vsBoPEuOoSfGU0BMX5rFzs41MMbs71EuHk w.ufZLIwQEU9C23BwV9GHIuU1spRBvA4hE6U9UB0OxiNQ4pYOtuxTpKPvCh50Vj4EdxyXe1h2nmw q4peT4dBWq141xJxwzsC7jnlIVzdMYjkWYVSQMf4IjGAmwd8lTAYkqO041SsN9FQtIUYiwrrdb2D jYbGxFnl8PHEaENQrIwl97l8fanItgWLnFwoBkPbBnTVJSqksTUReqjPqduSZpyFmGvwroE.eco9 RA1nHAtivlOl.z.v7ceVrr_3dwTRAqfDA_0NLb2B3sv75aWwNkdslRklyZWamr.Vjl.vWfH1jVOn 8FSwUGrZv7Tih.0B7cb6NpZdWl2zxqrL5yzxAHNqzoui1kWp07ZHqKXYellsAVn_fZshp_fGFOYk riBPoHq2TjOyJmhuMI0dHQNX5xKgjCHpr4CID9ww1Kg0xaEDhBzBj1icLPrYiJOoYqpBdQWtCpYi 85TnXf6SBgLKGuVBZwyEcqP4X75NYlHD3aAZPZTKi3uC.fpa6rWhS6jpw0d5M6EZ07c979O8CJhT ZVH9gLEaJWmTiWoa1Ynf8bIko7A4jHQ787aCdK39vR9SplMLb3nz4YfsKiG18Bn_p5JLyaQTah7T 3LxnpPi3pQZVRmBy15uzhIbtifsOXxR5ZrG.cMmPtKxM4bAHvcYshy4IPBnbAeqwODuEelq0co_N TFWVL92QW5p2fhe2BeR2Mja9hhFoUz2nbpJnwUmJp1G8Ud5mgQ.HTO4nrMn_0ZJF45oqrzvVMdct Oiw2oFFXXTL0CyvjmEArRuLIgx5knI.LOlSWcZ5VeWw7X75EnZdZ9hnnEcIpB07vu1WnwJI61BoT 0I9FVTCOfyrnnDKz16CqK7Bgii6.FsoYfYm0zPCcoQEgYe9sdUfhNxL.oy2jlqFcmjWxtIyxCx.8 vriGhDh26wP4HMgg_pcTCGhlvqd17x8EvqYLXL7arprweH8WjsnrmE1DHXLzVkVTmaWqL0thsWQN UU9CxmNUSJUZhHBb.r1UZCh8fCZx8AzQZwT6jpMr0Piq8TmkCzFF84wnSTsraVXZq1ri1V9e7q7H 03XhwgyzTP1qm8YyQ_3NvzAasP1dsARtIBVUuDDGJHPe6ibNV6tIc_4w53XKuVQm6R7m.fhqaDmG 92m4pSaiAXdGHS.vLL1A0sSSql4v0ea.0o6AJ0DsizYyqYxWMy3YEl3TnxEmuaKyguv8ogBJVaNP _.LYQ6q8yotF9feLq9HUmNg6bcYASlF9tqg4Tlvsd5u20UzveuoOBUHEkQkxBzpemk_Gg3HJhMBD bmLnLyzpoPHi7EhiulM7d.x.vUd.2GPCQXAgiM0BjLpwk0HZKYFk00VpLUQZMawXF46EZ.2oTipl MmTXva_FlILGoknYVA7GJiACEBt.zrF38VciwNb3lR4FZM8SEE9cqqV1sLWlfenPo1FPR9wYltOh pONwtPuVTSDe0MNmiTFGZHJAd3tJhJIhTcBl1EuSc1TQLGj2tK5O2l.FIMjlhPzU_ug5pA0313qc okDlwPi.6Y_y6cM4eUTOLpGnxc3z54eQ_ybzwXdjx2gM6AAi.TUqujDokt6W3oklfu72SzZPfY1p eSqidawyNBoEu8XFe.6tdYLLsuolIf1fETVy2UPE1Jh.vK1v1CBkrDQhKSBl0BIucixPLU5Jr6ER TYAysgjsHm_0e156Wa8i9Wr89lvCjXI3psNNWKilE3GvxiKPZPfmlrmyP1jMl2xBzwMXhyrESNJE tnHnPYfQx2iOMGXrUmCXb4vxoGfUm2w44gHzfqiv3c2JFyM5MRTuomaObgvhtkWbwcZN1CVjg501 lh2Ij9s_EHGGZBup6MuSrWMiZZDUYGH_7yUx_TMFQVzstPAcoISwGfsD5oUwV1h8HnHaQLya_F5n uPfkloPlk095OFxWgURihbdF9DCEz9ZY_F3WhvEgUio2v4ZhxND9RHJadMJySCMxSI4ui.HFc0Hx TsvSUowmKF95bzkERpoxV98lX7PULGJfS1GJAsl3hjsxMZul96p1IO1ZIgjkkH2H9rZeJ3.DhLtS VvJulGKXKAJujV7X1vw7IUUpr66Qv09o_zgLXeOFtiBuFX3XxROe0PyzWlSN6 X-Sonic-MF: X-Sonic-ID: f9c8c4c0-e15c-4d7c-8703-24c20ebaceb8 Received: from sonic.gate.mail.ne1.yahoo.com by sonic305.consmr.mail.ir2.yahoo.com with HTTP; Thu, 5 Oct 2023 09:20:15 +0000 Received: by hermes--production-ir2-788446cff9-7j7vh (Yahoo Inc. Hermes SMTP Server) with ESMTPA ID 07d37091395946fcfa95b4230d1b979b; Thu, 05 Oct 2023 09:20:10 +0000 (UTC) Content-Type: multipart/alternative; boundary="------------mqnS914oYId9taZudxelA0ua" Message-ID: <18a6d772-aa0d-4710-8d87-8480aa51d1fc@yahoo.com> Date: Thu, 5 Oct 2023 11:20:10 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Index scan is not pushed down to union all subquery To: pgsql-general@lists.postgresql.org References: <61de2059-b56b-45f4-98db-a5d9b982fa4b@yahoo.com> From: Marian Wendt In-Reply-To: <61de2059-b56b-45f4-98db-a5d9b982fa4b@yahoo.com> X-Mailer: WebService/1.1.21797 mail.backend.jedi.jws.acl:role.jedi.acl.token.atz.jws.hermes.yahoo Content-Length: 6422 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------mqnS914oYId9taZudxelA0ua Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit >> 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. -- regards, marian wendt --------------mqnS914oYId9taZudxelA0ua Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
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.
--
regards, marian wendt
--------------mqnS914oYId9taZudxelA0ua--