Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1llftE-0005nW-EF for pgsql-performance@arkaria.postgresql.org; Tue, 25 May 2021 22:51:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1llftB-00046n-UH for pgsql-performance@arkaria.postgresql.org; Tue, 25 May 2021 22:51:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1llftB-00046d-Ji for pgsql-performance@lists.postgresql.org; Tue, 25 May 2021 22:51:05 +0000 Received: from sonic310-25.consmr.mail.ne1.yahoo.com ([66.163.186.206]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1llft2-0000ma-7t for pgsql-performance@postgresql.org; Tue, 25 May 2021 22:51:05 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621983051; bh=jIN8/mA7rRsdOzZVZIxB/IE/sMkbAX7wNW5zRxJr+cM=; h=Date:From:To:Subject:References:From:Subject:Reply-To; b=CY1kGRvfk6y04mxmRUmHvmQTtVP5qjYtVNP76Qj2AHQ1BfSZ3km3Wn7SUKMQKpbl0Up4wYQNLH6Dr9bvtTpIUeAd21aTKwi+LYwmoDDhqGYuvzXuJBMG2vCmZDU3XyQ8gopb0Q/AexCas6hI7CWbhEWOnEFhcaNGPCKCyKalLvetUwlZSszPMbCZah8l20MGP4P1c9ME0SQEnDfHqtQw+xfHS6n22M4n44htXVD4BZG3jizvFLG98+Bokbjx6yEkG++mkHSITqeEKK+vt0vK4GXZXQrms4/JOjP+nCHv2qh8hMASb9r1NdSwbSVwO03VMB8k07VdYstgBkjSUYDWSg== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621983051; bh=xPId6lE2JVtOE+VlT8i/R0U05sZa5K7uRMFBKpPs58F=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=isJM12ZPC89QACBLf/tXeddvx9zAzOgAInBCPkaKSAuqBNgnSduIJ9kqoeZPj792N9jCJFDXKOHtwKxSNxT4PHmuWn8yGS9SqHFF0IRAjD918ZPCg8cbuJdzlXRKFFDSRT/WNxBJbpX/ZuQKCgldWDIjoTkN7/MACZ3CXz05i05Zwl3z+tU52VvrKr5Ej+lv0wXVxR3t+yUOZZsUjtjMzHv7C7GADjev9tjMP2d3x8hKIaC7N9x80tQrIdBvvQZzDeQG1aeanyzmiHnORoQupABPWZSDWJzzP8mi/0s05DWZo1Ecq33dl5x7p5/BCS1kHdJ4Z1ZK+TcGSxwd9TgTfQ== X-YMail-OSG: V4K9HecVM1nQkO_jdqSN74lDdV1G.78NM4qvk5eEWqYSitZ92_TSMpC13sSuwec 9nIPnyD6EGdj7x89zYdsfUttBt2M1_wLOdmKFbWpoJgOypsxyJn3OyH6AIa38vpKg8mLt2r5_NaI PN6QCXjfg.YzSfOELIQUK.DB1KXwHaFot9cY5Z_6gPD.zAAQGE2Hi_dwfDbjxdxp4BUIWFOX3fhl xmdCb148ETivRfL9G52vMJ3jVENibQm1MG37MNvWHj4WEY8GPTVJmSUcmfJzVCP95rIZ9_UeihSE qSYvqKUtWdeh7PGJmHfZYhN4t4le6Yu9zVKIkiz42OvcSgei2i75vgdFj7WUhp.0pkj9lTxoyPHh x6Ak4VlpSZIN3ywID64DRPRsKQfjexbr25dHJv2BHOLT8Yr7oEX7ry8abWA2OrrxkwLJ1Gqm8JZi RHeVP3eQ10_0W5CJ2lO5aOWT0FApL5DF5YgFxqwSE4hz1pUJBK0MYsY5SdQvMgvZ_HzfC8PToBXh cHlkRdHS9lmKk.iOtPzSDLXAYgkD61cwvb1k64LEyXC2JuivwKDQGFWrrDHVVg7KEI.TmqXnW.zL FgHL6ZFSTWO8KPAKryxcd5toamXWgq_nCGSJZO6Qv9yJ1.PvFOkFqsho7Wj7qYyHTTX8id7PFcAY kJ4RPeh1wTLkQcOLVKnrfvLS9iTM2UpOtb99_336d.jz5IspV0RcUauAkx.WRV9JjXb9AYXQx4La r8f8I0n0Y4RVSC5MRQoprahhBxEk2K1skxHQ2E4qI8kmqkTJex98RHpBvlv4v3kIWAXDeK6zU0sr VEaeLH58gasskf263PVYpi6Gj5AJ9Ra8u7J3AAE6QhWXs2Uckg3H4myrQ3RdoE9P1zYXZm1PgG8R P67H1QFJJBmt9x6ZCpr3WSJc0RvPBnkoJffIp4t0agjMl6DStKD1hnuFB5u0gqMRxSp9RLm1LQww dB6.IKYTBxAuzyhb1jOU3rRx.xktQhjCIQhAqTFtBIr.Btk7ycSfzmT7wuccGyPGUspb9.yYaj.r .VP1htd9QScoymOTTwKAFVi6CDYIGPDAFm8Qj0yg_tZ.IGnSrltL31hbPN66OuadWxU1JLQQwcHx MKhlk3STdvNYBGbyixiK6TVzmKfA_vlgZgyqw8SaJ2mAB2eKPCZbQ8yZ3t.cQNI5epoeq_A3NCNW RF.bMGlH3SnPVMCsTmEJ6A5J.EVid3puO8UFu738lQI8DSwbV5VdLOVXB.9tgTNo5t91dAvRJoE8 gOSOt8uL33tOKpLQe0_8PN_dFQxLAEa9IsxhjQGtCQC7btu8yMOHPoo5W16HZ7S0htiDwkj6g5t. KlUvZo8J0yVqpxxXXsLVrmMiK1dmnuFiDceWgBuBbcf1eFJLuYeVfqBW_9vLfrvH7vdrrGw8_7ia jy3FG8Y4yxLJdjfOC0jX4r.NUSMA_md2eJKHKhqhaOIexWm5IZwZ51396uHjjOz_Hz4hjr6FYkQ4 rg5F3tc_t0Hdu34MAHmY754jWNb043Lr2ILF6ELED6LVKIBzlBm5a0mOS75mcBGR4Zm1A0qlotba 2rceKLSaQFBliTjcN1xU2C0.bAA.kN5DuyxBzlMyq6BYnDN9CFbcX0iDBv8EA.FYnBDXw3fPSmSo 4.CJC5IRhcXTRM1oFgDLD0avlQxHnz0BketY8DYJPHxCMO3zg3R1mYPYFOZyihQAiWDpTxqsLZOh Tp7fR96mYXIgxWTIsW.OYw32vzKU_1WozYXxKrLqBgaBbVo4ulDN2LxqtZe6OXwA5ZLr65wEsZRr Z22LolvOO_6vsROOkLrpNjfG0k9NAQeum9d8LkdQ8QUqW5DAZbXQSoKwsly3LLLjxug3dF4TZgT0 USM08urDoke_5O4GNCXWsBNW49rS6yAejka_QgLM42utuT_6ScdoOxSn5XvV1sH4NoUuei1UDPzw rQhSOfyRUSzHrYB_tpbqKkOlfgU4BKiurKu50KAR8Zn4392LglynsLYocN91M9pLuqM6pCCwjXWp Z2F__tQOxkOgfLRHV3LkxIKreZksZnMouuVpW7c7fnO2QFAIQu24gir.oYUbJ9II8kFT70U8vI6I aYUGAma.SZvxIpZblZMN80zSVzTs78CcEwS2lzftmsPE1TElaPj6M4CjiJdZgGr5RVUR7GD4Dyaw P6G3Wg4yZHR95Bmf5XLz8jXgHw111I_OQkI0j2kcZeUg6LuyTuKhjDYMRFlEq5927tJWPuCC8xTX TzU5ZZwUUZ0oxVvnopUGONvCe0h1af2aFywhypzVJtxoRVbUimptogK7ugEOLHMdxSI6Mgy.u_xS tNKRsY2j5kWt_6gxTJg4Gdat0fLb7hjUezHxjDbaosaR60OqU3nMsmmZ8ruNJmobNgpTEcdI9 X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic310.consmr.mail.ne1.yahoo.com with HTTP; Tue, 25 May 2021 22:50:51 +0000 Date: Tue, 25 May 2021 22:50:48 +0000 (UTC) From: Nagaraj Raj To: Pgsql-performance Message-ID: <44089786.116151.1621983048012@mail.yahoo.com> Subject: issue partition scan MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_116150_2064398034.1621983048009" References: <44089786.116151.1621983048012.ref@mail.yahoo.com> X-Mailer: WebService/1.1.18368 YMailNorrin Content-Length: 7989 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_116150_2064398034.1621983048009 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable I have a table 'sub_soc' with 3BIL records, it's been=C2=A0partitioned and = indexed on the soc column. when the user is running a query with left=C2=A0= join on this table and joining some other tables, the query planner doing a= full table scan instead of looking into partitioned tables and index scan.= =C2=A0 SELECT =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 t2.cid_hash AS BILLG_ACCT_CID_HASH , =C2=A0 =C2=A0 =C2=A0 =C2=A0 t2.proxy_id AS INDVDL_ENTITY_PROXY_ID , =C2=A0 =C2=A0 =C2=A0 =C2=A0 t2.accs_mthd AS ACCS_MTHD_CID_HASH FROM =C2=A0 =C2=A0 =C2=A0 =C2=A0 public.sub t2 Inner join acc t3 on t3.cid_hash =3D t2.cid_hash Left join sub_soc t4 on =C2=A0(t2.accs_mthd =3D t4.accs_mthd =C2=A0 AND t2.cid_hash =3D t4.cid_hash) WHERE =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0( ( (t3.acct =3D 'I' AND t3.acct_sub IN = =C2=A0( '4', '5' ) ) =C2=A0OR t2.ban IN =C2=A0( '00','01','02','03','04','05' ) ) =C2=A0 =C2=A0=C2=A0OR=C2=A0(t4.soc =3D 'NFWJYW0' AND t4.curr_ind =3D 'Y') )= ; If I use AND instead of OR, it's doing partition & index scan; otherwise, i= t's a full scan. Can you please provide suggestions? For DDL structure=C2=A0Postgres 11 | db<>fiddle=C2=A0 |=20 |=20 | |=20 Postgres 11 | db<>fiddle Free online SQL environment for experimenting and sharing. | | | Thanks,Raj ------=_Part_116150_2064398034.1621983048009 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I have a table 'sub_soc' with 3BIL re= cords, it's been partitioned and indexed on the soc column. when the u= ser is running a query with left join on this table and joining some o= ther tables, the query planner doing a full table scan instead of looking i= nto partitioned tables and index scan. 


SELE= CT  
        t2.cid_hash AS BILLG_ACCT_CID_HASH= ,
        t2.proxy_id AS INDVDL_ENTITY_PROXY_ID ,        t2.accs_mthd AS ACCS_MTHD_CID_HASH
FROM        public.sub t2
Inner join acc t3 on t3.cid_h= ash =3D t2.cid_hash
Left join sub_soc t4 on  (t2.accs_mthd =3D t4.a= ccs_mthd
  AND t2.cid_hash =3D t4.cid_hash)
WHERE
  &nbs= p;      ( ( (t3.acct =3D 'I' AND t3.acct_sub IN  ( '4',=
'5' ) )  OR t2.ban IN  ( '00','01','02','03','04','05' ) )    OR&nb= sp;(t4.soc =3D 'NFWJYW0' AND t4.curr_ind =3D 'Y') );

If I use AND instead of OR, it's doing parti= tion & index scan; otherwise, it's a full scan.

Can you please provide su= ggestions?

For DDL structure 





Raj

 ------=_Part_116150_2064398034.1621983048009--