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 1llgcm-0007Wc-LB for pgsql-performance@arkaria.postgresql.org; Tue, 25 May 2021 23:38:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1llgcl-0003T7-Lc for pgsql-performance@arkaria.postgresql.org; Tue, 25 May 2021 23:38:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1llgcl-0003Sy-8Q for pgsql-performance@lists.postgresql.org; Tue, 25 May 2021 23:38:11 +0000 Received: from sonic307-10.consmr.mail.ne1.yahoo.com ([66.163.190.33]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1llgci-0001Gb-9k for pgsql-performance@postgresql.org; Tue, 25 May 2021 23:38:10 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621985886; bh=bumJ+zLO4+ZgNk176A79SaFVAP/pbMdEIQvXqf6JJ94=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=fCoNVIUZbeY/YTyWuxRLX3xBoWW8Gl8FCVNCj9ieSdqlvG2mJfJYvhSPCoj/HwwCuxL4mu3oBM2qBiVn0L+Ue93lSeLGqOH5Icv+FknSN75cP6ZQmqoYICinKdH2bYXY8GUHdsEfUeuca9789hMDoyIC9lXNjCkHw5Gmp+lHIQLYK/HBCaTyY9gd87qz8PWF1ip52W4216JIKZP+vYL1ioHWDIH8XMKGfgrdaMvfa0pmXHGv6KekZtDNugHOs15ix/GPRDU5bU/YtpYKhDDirjYeNcnZmvSC3oGpjko6U8x9VWFeXvHGF2c8QYs0Yo2ory6M/OklwEUBju9e/LRCIA== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621985886; bh=jHKESQ0LDMzNCi7pQ0ewiC6+ImykZ1liSdQVALiMCzp=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=oWCg24mpf7kz5VbGHH+u5LawmSLvYPxdc1qw2u1ijD/MRGj/k/qJQTb1GJXsVuKLsqtDz8FfE8MGshbLfLLBksUmyWFiQ995Kp3649if6pHyBexXm+56azWOCGdyPGrM6GlgaWj/3wfgO1TAUz1p+ujQT4wc6Lxq9yjGEl91fE+MwNEqRCfbN3yaOAVyMF0qK/+8ERYMHES/n2R2NKdq8azDLBEMFXIXiKdZibJKqmofk7GmGfNA9+e2fYIJGmGZK1BqzQGVdkj8UTuhbif32oppBm583PUSaUIUET8cuqEQh2D7DD0XUNiIxmZDzLebg+kMopHP3sWJhyoXj99MHA== X-YMail-OSG: UC5tvd4VM1mcIszeE9D0yw7sACCVyjqROuNVgdBBBA.j7CbOxgsrTuZWE2tnKuj HfR8xk6inCFy9wSaNOWzJ79y0eaC0bpUDmcNOb08xeCnBBwXetgupFFit1C3mlM8cOLbNueGH7j9 gpKeyxFXlCSN06VUmPUnhQ7J.SkQmqGj.RpdWuJMmZxED_J9nZ63oaaZAo7mjnRwlWO66HDXFw85 OaApw46rKrb3.lFtKSFxUfPbKtiCBQnCTBJ.kWcPT3EiFNGiVGRLVz7W9YJf8G5VQzaSuQ2Li9rm YOY0Ju4A1LAuaGeFekfdoiA2P.pGhI8ZHBuk11GNgExt0GEmhAy4c7UWblTMwxjmF6c3NRsMhDSC a1as1yDLqxFLqE6cxZAAtgp6z029pcY.OdTQsjejgrAvUQsD66SmUeDqUcSGfoFLqeprvfsDdmPt _fJ.wB9J2sy6Eft1qpYQ.8WziYKRo9QcHvnnmIYnA43xH8GMR95fL8qUomOXBZ.toWV_1Es9WDpx VZ6ZWOijIbG.WOwJ4s9SN42.ORKO94UwsKJEHq9cGdglkP.G3c6_9lFJT.4Ahu.7rvOafj6bCJrG CqK4OKChd9CMOZLstm5h4RsGCwnIcNjxbLCfmZW9yF7L69l8ZCBLaAUgWzajq9LpJMkHshcLSXQd KezeZKhNixB_BYbN5pxbj9DUvNX8hVO5Jg3gxB2CHFTvbRRQlZLek5ShSHqHAoIilq9RtXjrXsQG xMVmPX2FbgKaS_cmWjBPTBQ9lDF6DrlbkjGYNINiOdUXijfF1Ous8O2g2BkNPgUR8v_fJfpLkoMc uWKY1Yx0io7BFyNvFRjiMEPjJjmLQoYdkthu3RXoUfFXJ_uTOwb0b1mnu_HloWL2w_0RYPPppUEM 2aKdWa90zM38hb1znD5JkZttkJrH9Mxv5IDKORrmiZko0LHxzpLvJ8HnLKoMd.i7e5kKNELoDCXA HqHI24yC75TLPNeyka5LjCQOtQ3DDFcWRV7APjP30E1X9Qk_QveCr8711Ru3YCPnA0XqEq4BpmQu d_FpFcVNHGDIfqzvW9UFY36VNHJfye14OuHZVwtsNmq3tJh_Mv7UJn.iew42fwFVlrj159.QRnIu KpgCb5f2g_E6oPIBf6.M7pd9VXPeo9Lnx_PxY6GiUppWEaBI5DHT8lBwMoViO88GKiU4kKcPamuM C25.KpUtHoBAeTeGA8Fa_DCkbG3cowTrrO.sjxLCTEL.JWFrMP3nptMx_kbtTLNoRYsaOkONqoXj e2NqhOE9NGHxvMc0KLMLbgtrbu1FmqMuPpPHCfPu9ZmH8ufRH6YBqWSi5JGbaQuVDSr3FNfOa2Fs oZioTlFcIRjinQnkpNS73Y4ls.Jg4XE.f4OyIQXCg_gBTReTh1xD3EOK7i5ZXnG2e7WbBtp6KDRn M4gTlellFjTSZ0aQOGLOf1NHztwmrnCACFozNNbrQs6y4GCi5QvgAc81raGnqodWUl0M06NTn_SB czcx70FovSgRVsU16N4oZ8u9H2eWvhg5OIQJqm2bsQ7tbsUFAq9AsEuTYb3jTWrRBVjdG8Xt.1jP GpFyZow9mE_g4qPxoYK7QRfDb2HmChRB0_tsVEoZEyGKlTbm6DUPqiWlQN1zAHz9qDUH9e1FM8eu kddnbM2ONVmdrNfmBLSSCgxdcX1HhaFR2h_.f_IZN072uBaMrlffAaiQnDjsP7Gs45T8GaQmj1ds HJkspUQ3ZD3XHyixw4jer12l4SnnV_RSY2XMgzNLzkpYH7u1NkkytqB7iUCfkqH2KFcx5PiIOeF4 oNp42KmfpX56zvhjR_qvm2pYkJ2bTD2RQvdBn5yfhtXErS4qJqSflH1c6E7Oe5hXikazgrlIOlMr y75XJax.kfTuPEWj_2aOr2RMICn8.E4yI3lYEbv5iq07j66Rv1G9g79inBUSo8MoXWifBB4z.6O7 RPENHt7Cqr2FVbY45myQMKOK2zNyxGaYdF3mogrs4Tffnwn8rmEJZ3zSwzoHON5CjVb23iqDVQcL W0E8IybBXWHuqdaKitFU5sELp_g1jo6VUteQ715SiP3IPjMFFjE42gWTEeQp7fwj_tfmPzyQt72o jUAOyFM6fVSi8PUouhemFZmtRmyJ7AoSeyZjnr7HitcnWTug01.UJW4kS3AlP1u7WGPNvT6vkrmc 8xwAp59nEP81H.HzHbLFOTnaISi2twN_QiLWRiNxSbVSaKAU1rQUbYoilKCreI6LlVYQLatbPVmA tFbquskqRHoibj3A8goebK4kxQ5zxMT.iwsskzwP.am_BZtFeX7cSfcA_7cSjwClIumi2UzkAU2Q NYU8IDo5LrEUx0unvAy340WwARvbcQ.2.GrBNWke8QgTo4DFMnTINafjXBDVvD.5O9TOxouYbxJv mIC6hg5q1r6z5TmEvaJH1a24cn0AHJDqW9MlKourlD..OXG3HwoklgiOVggfn.TBwXA1jNZ5WLcz te2Al_wfvwXbn X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic307.consmr.mail.ne1.yahoo.com with HTTP; Tue, 25 May 2021 23:38:06 +0000 Date: Tue, 25 May 2021 23:38:04 +0000 (UTC) From: Nagaraj Raj To: Christophe Pettus Cc: Pgsql-performance Message-ID: <267690084.131091.1621985884945@mail.yahoo.com> In-Reply-To: <366856CE-2043-4163-9753-A735B02E81EA@thebuild.com> References: <44089786.116151.1621983048012.ref@mail.yahoo.com> <44089786.116151.1621983048012@mail.yahoo.com> <366856CE-2043-4163-9753-A735B02E81EA@thebuild.com> Subject: Re: issue partition scan MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_131090_1191297881.1621985884943" X-Mailer: WebService/1.1.18368 YMailNorrin Content-Length: 7015 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_131090_1191297881.1621985884943 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Apologies,=C2=A0 I didn't understand you completely. > 1. Those that have sub_soc.soc =3D 'NFWJYW0' and sub_soc.curr_ind =3D 'Y' > It can use constraint exclusion on these to only scan applicable partitio= ns. > 2. Those that have (acc.acct =3D 'I' AND acc.acct_sub IN=C2=A0 ( '4', '5'= ) )=C2=A0 OR sub.ban IN=C2=A0 ( '00','01','02','03','04','05' ) > It can't use constraint exclusion on these since results can come from an= y partition. Why is it not=C2=A0using constraint exclusion on=C2=A0the above two conditi= ons(1 and 2) included in the where clause ?=C2=A0 Both sets are pointing to different tables. On Tuesday, May 25, 2021, 04:01:53 PM PDT, Christophe Pettus wrote: =20 =20 =20 > On May 25, 2021, at 15:50, Nagaraj Raj wrote: >=20 > SELECT=C2=A0=20 >=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=A0 OR t2.ban IN=C2=A0 ( '00','01','02','03','04','05' ) ) >=C2=A0 =C2=A0 OR (t4.soc =3D 'NFWJYW0' AND t4.curr_ind =3D 'Y') ); As written, with the OR, it cannot exclude any partitions from the query.= =C2=A0 The records returned will be from two merged sets: 1. Those that have sub_soc.soc =3D 'NFWJYW0' and sub_soc.curr_ind =3D 'Y' It can use constraint exclusion on these to only scan applicable partitions= . 2. Those that have (acc.acct =3D 'I' AND acc.acct_sub IN=C2=A0 ( '4', '5' )= )=C2=A0 OR sub.ban IN=C2=A0 ( '00','01','02','03','04','05' ) It can't use constraint exclusion on these, since results can come from any= partition. =20 ------=_Part_131090_1191297881.1621985884943 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Apologies,  I didn't un= derstand you completely.

> 1. Those that have sub= _soc.soc =3D 'NFWJYW0' and sub_soc.curr_ind =3D 'Y'

> It can use constraint exclusion on these to only scan applica= ble partitions.

> 2. Those that have (ac= c.acct =3D 'I' AND acc.acct_sub IN  ( '4', '5' ) )  OR sub.ban IN=   ( '00','01','02','03','04','05' )

&g= t; It can't use constraint exclusion on these since results can come from a= ny partition.

= Why is it not using constr= aint exclusion on the above two conditions(1 and 2) incl= uded in the where clause ? 

Both sets are pointing t= o different tables.

=20
=20
On Tuesday, May 25, 2021, 04:01:53 PM PDT, Christophe P= ettus <xof@thebuild.com> wrote:



> On May 25, 2021, at 15:50, Nagaraj Raj &l= t;nagaraj.sf@yahoo.com> wrote:
>
> SELECT 
>  &n= bsp;     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
>    &n= bsp;   public.sub t2
> Inner join acc t3 on t3.c= id_hash =3D t2.cid_hash
> Left join sub_soc t4 on = ; (t2.accs_mthd =3D t4.accs_mthd
>  AND t2.cid_h= ash =3D t4.cid_hash)
> WHERE
>&nb= sp;         ( ( (t3.acct =3D 'I' AND t3.acct_sub IN&nbs= p; ( '4',
> '5' ) )  OR t2.ban IN  ( '00','0= 1','02','03','04','05' ) )
>    OR (t4.soc = =3D 'NFWJYW0' AND t4.curr_ind =3D 'Y') );


As written, with the OR, it cannot exclude any partitions from th= e query.  The records returned will be from two merged sets:

1. Those that have sub_soc.soc =3D 'NFWJYW0' a= nd sub_soc.curr_ind =3D 'Y'

It can use= constraint exclusion on these to only scan applicable partitions.

2. Those that have (acc.acct =3D 'I' AND acc.a= cct_sub IN  ( '4', '5' ) )  OR sub.ban IN  ( '00','01','02',= '03','04','05' )

It can't use constrai= nt exclusion on these, since results can come from any partition.


=
------=_Part_131090_1191297881.1621985884943--