Re: issue partition scan

Apologies,  I didn't understand you completely.
> 1. Those that have sub_soc.soc = 'NFWJYW0' and sub_soc.curr_ind = 'Y'

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

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

> It can't use constraint exclusion on these since results can come from any partition.
Why is it not using constraint exclusion on the above two conditions(1 and 2) included in the where clause ? 
Both sets are pointing to different tables.
On Tuesday, May 25, 2021, 04:01:53 PM PDT, Christophe Pettus <xof(at)thebuild(dot)com> wrote:


> On May 25, 2021, at 15:50, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
>
> SELECT 
>        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_hash = t2.cid_hash
> Left join sub_soc t4 on  (t2.accs_mthd = t4.accs_mthd
>  AND t2.cid_hash = t4.cid_hash)
> WHERE
>          ( ( (t3.acct = 'I' AND t3.acct_sub IN  ( '4',
> '5' ) )  OR t2.ban IN  ( '00','01','02','03','04','05' ) )
>    OR (t4.soc = 'NFWJYW0' AND t4.curr_ind = 'Y') );

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

1. Those that have sub_soc.soc = 'NFWJYW0' and sub_soc.curr_ind = 'Y'

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

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

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