Обсуждение: 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') );
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
Thanks,
Raj
> On May 25, 2021, at 15:50, Nagaraj Raj <nagaraj.sf@yahoo.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.
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.
> 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@thebuild.com> wrote:
> On May 25, 2021, at 15:50, Nagaraj Raj <nagaraj.sf@yahoo.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.
On Wed, 26 May 2021 at 11:38, Nagaraj Raj <nagaraj.sf@yahoo.com> 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
> On May 25, 2021, at 17:16, David Rowley <dgrowleyml@gmail.com> 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, butexcluding the partitioned table from the JOINs for the branch of the UNION that doesn't appear to actually require them.