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 1qoKQL-000tR2-6X for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 09:13:37 +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 1qoKQJ-00Asfq-4X for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 09:13:36 +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 1qoKQI-00Asfe-7B for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 09:13:35 +0000 Received: from sonic313-20.consmr.mail.ir2.yahoo.com ([77.238.179.187]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qoKQF-0007fx-Fn for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 09:13:33 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1696497209; bh=fQB8I/wJqJtqKe/FoqauKpMUuMvwMyw0uJdQKFcfwME=; h=Date:Subject:To:References:From:In-Reply-To:From:Subject:Reply-To; b=rOWnq/6jfqJcjdNz5zeE3xv9mBABDTOLFrvQXLkROi8x6QtalD6NZGyyhVGQw4MHbTB6IawzdaJsF4N6fmFSMfcJ95kqZAFRnqyq/gqWZgQp0ZYmWnw2CcufwRzxYig4v4FZ23hP9qJFrSP3cr9WredEstG0/DXEVrhhtLbO3eL0mEpuzYcbK2UouSHzqcVCzCXKOO72iwFXyzmKpVEYHtPH7Xkovu0u83jsb06W8MowrnuBxYm8E+QerT5niaVlu4dSu5dpArJedS/4zZO2gncuEt2TLsakNCtZQxpACnH4azbg0I0JfhGzRr+ddlExYzRLphqMYDubQNlZuHy+Ww== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1696497209; bh=qHVq8+kvKg4NpHrSIOTFhqcrjnOjsKiKSv/8Hq5hGzf=; h=X-Sonic-MF:Date:Subject:To:From:From:Subject; b=na0x91s8ptqel3z3KTD44+mLBJIm1y7uolBakNztF+OU8iXifZI1ToT4rw1CRfg1JSua6lHSBazxqZ6IKkp+3pE3kdQbS+cViEb9Bn0eecUGm0JO+njiCla5yAvzbf6AQnz/2z8xXPkLI1Z4boBu5lQ3nKT3QBPunm0WT18ArDA/NjyFLEkM5L1xTERk6hGTvkJZRf+EcT31hcmu7yAbPiWTV4/E+6O3leKG+r+ISXbQX0d/6L82dwPMWio85F/UDSF23XZxzD4mxkd0WZDvelnki0oEbhxROK1+aoZoVxxdfKj0qfGl31X6xFu1Nri+DZO0B+BuJeVOQ7BfEFls1g== X-YMail-OSG: 1cvZr.0VM1loF_g_lkiX63XVzqF1QLsWLBgxr_qKRmWQyCJlS65oRzBEYzCcnWq RnOMsL_0U8_jIAN.z.BWWGSvs.yoAumzhNgS5PTZWMYa7wM0ci2m2nmIHxTjCRN2_tsYegdcDGO0 DrQeG9Otqq9uAn5EsOqaWgDHxvqda1i2UTVeJzELfYk33c0mRZceUDPqEiF3AS3cMQChyKA2_VPJ LBUFVtxzN0dxpAEijrnSulmaGaZ2ZqtrdL5uS49akejT0Tj2Sd0U.C2lIShXgJvEBW4qkbnRb3fy ZdAXmMY6s2O5k5lDrDmvd.jU0mcCmOllKuLZhQV5l9IRowCb4AWG.8sWMTpTFKhxux_qiipJp9Zl gqH0UnqJFefzlAxNzjn6S4U3Tj89XmM6cbZpwhtHIzRwYZYVKt.UZSwXOTw4zcjhiq2Hgh98PWmz yf3C9cFp0gjS9SBdASGCT7kijrB9JgmcFueZIzYFSaCiyUhzKh3693vRvB8ha1YCU5_05NX3nsV7 j8DD7LAyF.H4gErUMFy.QL4lHrQQbpx7WPbQccx2xfuOxeQWZ.vfmvHdqIPMWhaOnk87Tn.xSLD2 MfP77oVhQCchT7Yk96F0Ip0xkLZzv1apMktd9s_5ka3ykQ2_WCIdoaOniViEFkgIaFXLzvcUQuQl 3TdD8QpfYQtBNDkVxFu_UHIyPaltb1nHs68eDWKZdSd1MUBvryhGoDTdTxQ1XFgrihSvCJVRiOc7 qbCnBR8myGXCLaU21RepFtrK5r.tPC.BwDnG2NWqrDk9vYc93.tke2Ohyg483UKY.LC1hEBsUt65 gWkamc2emXjKc88.ptOs0AtS_O4hjKcnSKIM7z_1MV2EZgnOAz0g1lo11B6YN7mgnvvM3B1wbnf6 KKpz0RY7assoN6dwlQJ3hDpnKGgRSF30J.MKa6V2KYkmLx2VYUIawCVlhwOYg8Cft2lokNmnc0yV IArsGL_28Mz7Z1uSHasRJLZCm1S_Kw3BNdCmPhLX3nPSMsD4oJGqL6GlkUxzNJzCzAfJAdZ_ofkp 3bOLSte20DnN.3tl1qIdkIdJ6JlDIZIWoASSkD_YwqilN55ZV5TMWGa0Ny7T.62mWAB5VkvdbBwH rQh1Z7oVloybL7TAfymakPnHwn9vXPI8Q36WXZB_tcvEel6.sQIdZAFuczuv556ZGPiM5Xrd_3O0 1HWU3VCFN3Ufq3i4gQQ595Em.R7QCxVNIpbzYFdQLmLkyeEKfFaiJmixxj._MTQM60eDgZdRHYP9 OqUUZKP7ZIxbAu6JkJZcmQn0QpggWhAfomhL6cQtxPwXTI_MJavmJlWL3oZVJzrwdeqUDxqC40uq daDZnJnFqB4.RqhbjPjVU6MKiu4h_nsd66uiCd5c7iGl6B0_qmZ1zC6i3.HWF0kqD8fPofSBInwY KxGSEsFfDrENTfWXpUhBAP_xfpTrH1uUUwQc00Xce4JSC4DObaB84qAaNOUL3hPNdSTYpycvlUHw pJjfM9Tg3plk57Fu.1B8hf0_QcWy8ndIRqfgLQ5jwjJvS8kySiBU9SfSw6DMZS7N1lINKNAutE3Y DGSjv8LczWZQSE1Fw0wlDwATSZJ9JV2cIr76A58YptVyaeUQAgVu.HYRve_tYQyMdmlBAdk5NdLQ XlFSP7.bKWf1BihecKm.hCRaYI0d3siHhCKXqROzNF1UMYsHikMnnFMQQScCqF6bvCBQc4U4H0n. XlhU.pO63w8N_cExcRQj.i1p4qbQxcTZaeGCcJ0QorSps9cmAakPBgFvcspvdR3pYRbzHTlnWIwt mkPg7nc.MUolwcsdme8yJmFOe3MNxFvYF1aPzZZHTAH.S27f6O8Hbdfc46N1UatRVLI5HMkv7I6l vYfAIWz9D5jg1nUHdqN2EFuxS.xtkz6X9Z.OEHwoxJx5Q6UUzX.3phHhBMs5Dk2_DJg0hHL7G3Tx SypUuwf2fx81p_e4Ww7oQ_xTPK21IBBne2U.YSqEus9hPMCWSpE7QJSxEEcfqcPsa0YdyZbNZJOW ZP.0xONOSDxxRGsPUTNlIt7trq9G48Zbu.CFOZfCEsVzIJ6pMpdkbZHerW6MyMRvqHh.I4rPm0jn JkWGqbIggbwbou0f_m87Y8qRCVvtnVFDr3YinRb1uMxA2FgXiyeqY1rahipO.ezqAUVw54IRu5wI Z9EwbjHNHQrIh.wyQh.xYXRUbBaUQOHyBeJf20zWecsg1yEJAmLIejYN1SRQ- X-Sonic-MF: X-Sonic-ID: ce278e4a-7cdb-45e4-a237-c1c0e522ddd7 Received: from sonic.gate.mail.ne1.yahoo.com by sonic313.consmr.mail.ir2.yahoo.com with HTTP; Thu, 5 Oct 2023 09:13:29 +0000 Received: by hermes--production-ir2-788446cff9-tqr49 (Yahoo Inc. Hermes SMTP Server) with ESMTPA ID 18e5c7be3c70be91c6e19ed518e9dffd; Thu, 05 Oct 2023 09:13:24 +0000 (UTC) Content-Type: multipart/alternative; boundary="------------9Cr1v3qzayLx0TgflI1pTG5N" Message-ID: <61de2059-b56b-45f4-98db-a5d9b982fa4b@yahoo.com> Date: Thu, 5 Oct 2023 11:13:23 +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: From: Marian Wendt In-Reply-To: X-Mailer: WebService/1.1.21797 mail.backend.jedi.jws.acl:role.jedi.acl.token.atz.jws.hermes.yahoo Content-Length: 5660 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. --------------9Cr1v3qzayLx0TgflI1pTG5N 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. -- regards, marian wendt --------------9Cr1v3qzayLx0TgflI1pTG5N 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.
--
regards, marian wendt
--------------9Cr1v3qzayLx0TgflI1pTG5N--