issue partition scan
I have a table 'sub_soc' with 3BIL records, it's been partitioned and indexed on the soc column. when the user is running a query with left 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. 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') ); If I use AND instead of OR, it's doing partition & index scan; otherwise, it's a full scan. Can you please provide suggestions? For DDL structure Postgres 11 | db<>fiddle | | | | Postgres 11 | db<>fiddle Free online SQL environment for experimenting and sharing. | | | Thanks,Raj
Re: issue partition scan
> On May 25, 2021, at 15:50, Nagaraj Raj 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.
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 wrote: > On May 25, 2021, at 15:50, Nagaraj Raj 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.
Re: issue partition scan
On Wed, 26 May 2021 at 11:38, Nagaraj Raj wrote: > > 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. It's because of the OR condition. If it was an AND condition then the planner wouldn't have to consider the fact that records in other partitions might be required for the join. David
Re: issue partition scan
> On May 25, 2021, at 17:16, David Rowley wrote: > > It's because of the OR condition. If it was an AND condition then the > planner wouldn't have to consider the fact that records in other > partitions might be required for the join. The OP might consider rewriting the query as a UNION, with each part of the top-lkevel OR being a branch of the UNION, but excluding the partitioned table from the JOINs for the branch of the UNION that doesn't appear to actually require them.
