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 1qoKkH-000uOa-5C for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 09:34:13 +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 1qoKkD-00BKSl-Db for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 09:34:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qoKkD-00BKSC-15 for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 09:34:09 +0000 Received: from sonic311-30.consmr.mail.ir2.yahoo.com ([77.238.176.162]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qoKk9-0009Fd-KD for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 09:34:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1696498443; bh=644n6XzOsWKyQp/xoAAJQWA410oC269+Peh4fKEjvJQ=; h=Date:Subject:To:References:From:In-Reply-To:From:Subject:Reply-To; b=V3pa9g9AbUn0PHhCnVkx2Dc14GAsh/ZRyG4j2Tu8mXysH2MdzvHM3iKW98zx0Bqy0UbFs3ZDg91Q+rzTsiHGnMSaQAuWOs0wfS+mcDTrMiGybxZYdGmycNhcBqFL7EYa97vyvzHPSmhQ440zpkMpR2Ra5Aslx6TAo/UhEz1x0CDPy32l+WfOVjdsChqJ3Zbl405owumouY0JmuuvkPgR7Xg+lw2AnKpHtHLIh1EVHy4eguuSCk7epTY1ODcC0vJvH07ePzQ8bXfV6Q8CXc+owLoVgNvM7387jHs1B2xSZgdBiSGMpACRzKtTiSDya903Q1gCtv9AzCphQNnxJ9Jf1A== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1696498443; bh=6Xkm9Ki1FCJfVzHq3m/N2tkVvoaKTpTTQpdw+d884gc=; h=X-Sonic-MF:Date:Subject:To:From:From:Subject; b=lscAj2NQvDXW/tSkQBuqSVQtT4rqA5ERUfbafBhxv2DNFFBBmGJaaFCU0GCdAKOA0uLOsF1Bz7ljBKQ2MJY5cZrUlvSnrTZLoa+1voT+jTs8EUREnujXsrtqfz9tVyEzX/Ob/VjseBX5SUgl2G9O/jscTo4MNo7tQjBqk1Pas9nGjlLZ7w5/yTKbQLh2YDE7pdZufjc94SJQiKWzWpgxK1VREQNvvgCYuYKeMTS3kBWb9At3ITT8TY3q1ybk5fWNGiwQJqZ0uquEHwcZxVj7jmqYzpgVO2+xPTZOp2qcQ8mxMfuZjWWFQk8Gi9CGG1v2InPvSszbSx/khOzfewJpdQ== X-YMail-OSG: uGO.uXAVM1lYd_.mhMhw0IHkWtkbXX36gbzhPwPB1fmN_dks8FA.Un3b693yYtw XaACCe0OxDCboNsj2HpRbmuJURrIKUhwnQ1i55Aj5kGxcJWSQZa5E4cfs8VUU4jxScEU_k0y_c9A jGk7rTXus_HbfWV9Of00d6fJS0SGDFAVd1ytKuEPlFaDSLMzrM43Qy4F6uMt9AInJVn8rDgYgqQv 2F2g5EcKtkeQC5LvE4S6Df25yX7x.4nrbiycxA25c5h.ZOYudNPSYZn6QYQEyb9NktOys2TSXZEB 8ddIPCNB7f5LSo8QATdto4q9dOroMHpMppZenulqheTrEPxdgPdMB5TODIA1oGBag.wGL6lpGIEZ Pd6TkDxGAv1e4X67JUrnGYmpMco6BEOkGacjaR3T_E8LbAAGYg5OAZdeZLw00cTSUzbpeHnf3uN9 s2.ZykQyKBo2S_vtdniIDp6.8RCAA6MoN9ADlOx7GPZllMCgx1ygr4tb2iccRqIbAem3l5m77s0f BqBmX14oiMx6Gl5sxXfCU7uhxHbbp3HQXThtkpYEf1uDLBOkCS0IR8p93qCJKhfXWAwBLuWzPrBk LbSY4hdXNIshlRom8trE2Xobu7jKWEViuJuAFuM7qKkAjKvW_Mesc3juOHjNjsykJVQIXfBmLjPa PoK4OCcR9zZ8q3_Th4KMzj4y4UctpKXzhTvjW7sAnA24th3bQkZPWha9LzqH9HErT3GEwOxIZCBU vekHhPxLjqd.v3ZfqvgGrcDxzyNow87nbn3xALcbqH0eSAEPWjTHzKfgXyuFoOMX.3UfA4f8a6HG iLtlqlzhBe4GhuLAc1nDVFk57wwJHlhUsIbLqHc8M.UizmjwEv2kD1Zp8GjJgchi5ZhnO29X6z_5 DWeLnv8fyqFCbV703fAgIQQ61jv3t_bn7Cyb4dGUjv8w7IYZWSzNDe8TyCwE57KL35xiY6ibq76G Lw9gkhnOa.2LyQzRK3JKXDnodzD3OB9NQCmL7AK6FSh0EC_N8ui60iIryOqmRTF0vkyyX1gE8Y3i NgAJRwMgJ7k0fQNNXDCL0Lo_X6z7DbDaNex10mU4oPY795yVAktgMOdZcFWIWn9uuUOYw4m6DUsz 25gonym9Ckrui5kU9XBkqZzRVvpo9XbEDcQpw2QyZm4DAw2bYdbdRbco_RCeLvHSohPltPsd2CnR L1aScTBKuwF4wbyLg8yr_jdanpir5RkAv37Sk9vFsATCpbBQcFkr0Iffgs68wYYTE3Cr5UdMZw3C u_EF6hmXI7NKVBI6iDYy5.9HK_j1B8oYW8CdqHvYrgVzbSG9sFl8Ap8DVtKSBLK8ex3ptosBNV4q kotERlvsTyB1Qk3Q7O6MM5PCFDfeUWNRK_OSIsuX1kApQjNkOBYeElzq6s17Isp4zPfYjHPPe9gC yPbvf_qFOk0ym_XjprAoixckx3Ef7R2n4YDwtd1y3Xy4ZAoT9FKjYDw7arlIP6.Q6eECP7Y3E7FM RyaPmM5JYqLIQ51_iDC0BM_VfpughYovNBtwJ9uSx5xQrGBbWoGdYtJc0n2zja8s4h15HRfZUa5o hdAj6H0d2PDI1yl49YCXSwtSatFRY4X5NJkHrxMzAr9oBu_RvAqLK0WlU8TBmWDivqfVjaX5POmt qQgzZvMzZtkwScFR9V.VPXPbPAJwXBpfwVZ6am9wsNo23vEtTLnRkyV1JRW5.Wz3RfK9HngMjWbt yBhXQbzHoNuwHf1xygoQL2J_m0nexKjNIefugzHjctKySjB.EyoVIajbV2oeaicSMZcsC7.2CGsZ dptV6vJW145QL7RFXBy2lgZS3F8FqOFCzQTgowoWc3s2IN6Hi9k1F0RGjJl8C9to7b9RxoWqWlT4 mokO8fGhJB5GUoW7Lo0ezBSoYlbaldXkU67C8uf.WZjeVhM283voYAtbaskOj2wGx7iLfOhSsJvS PlHxUUHvSYkxlqs4ZMoricTl5smytzk9.fDd.v7nahHDEIsLYauEWtzsOIuvUFk5o7QcIAae_z4j sVohNYWqFT1K60TdlGJUwIlsPXNmrDH3kWGs3NWNJlbfPWeJFBrhv30FiaW2z6iKaqc9rjJ9AFgB HtigYDFcGa4sUdPZbStXCE4GAF7dOZUsUdhC5SwzNpJbEyUTe4OsxaxM9tJ2j8DoP3ynJKc4a54K fWeoh2i71o5JqanyGWVqI6CeGOHYOpgkcdW0qgO0jxoXP1xOcw9uzIGiTUo18 X-Sonic-MF: X-Sonic-ID: d17c7bd9-ce0c-49a7-bf2b-96a98cd5d101 Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.ir2.yahoo.com with HTTP; Thu, 5 Oct 2023 09:34:03 +0000 Received: by hermes--production-ir2-788446cff9-7j7vh (Yahoo Inc. Hermes SMTP Server) with ESMTPA ID d3ae817e10347add3377273cc1ad9776; Thu, 05 Oct 2023 09:33:58 +0000 (UTC) Content-Type: multipart/alternative; boundary="------------uOJ53LRzio7AdRgRKB8WuJ8D" Message-ID: <3089ff2c-2c50-4503-bd13-80452562756f@yahoo.com> Date: Thu, 5 Oct 2023 11:33:57 +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> <18a6d772-aa0d-4710-8d87-8480aa51d1fc@yahoo.com> From: Marian Wendt In-Reply-To: <18a6d772-aa0d-4710-8d87-8480aa51d1fc@yahoo.com> X-Mailer: WebService/1.1.21797 mail.backend.jedi.jws.acl:role.jedi.acl.token.atz.jws.hermes.yahoo Content-Length: 7730 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. --------------uOJ53LRzio7AdRgRKB8WuJ8D 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. > 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 --------------uOJ53LRzio7AdRgRKB8WuJ8D 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.
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
--------------uOJ53LRzio7AdRgRKB8WuJ8D--