pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedissue partition scan
5+ messages / 3 participants
[nested] [flat]
* issue partition scan
@ 2021-05-25 22:50 Nagaraj Raj <nagaraj.sf@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Nagaraj Raj @ 2021-05-25 22:50 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: issue partition scan
@ 2021-05-25 23:01 Christophe Pettus <xof@thebuild.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Christophe Pettus @ 2021-05-25 23:01 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: pgsql-performance
> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: issue partition scan
@ 2021-05-25 23:38 Nagaraj Raj <nagaraj.sf@yahoo.com>
parent: Christophe Pettus <xof@thebuild.com>
0 siblings, 1 reply; 5+ messages in thread
From: Nagaraj Raj @ 2021-05-25 23:38 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: pgsql-performance
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@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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: issue partition scan
@ 2021-05-26 00:16 David Rowley <dgrowleyml@gmail.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: David Rowley @ 2021-05-26 00:16 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: Christophe Pettus <xof@thebuild.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: issue partition scan
@ 2021-05-26 00:27 Christophe Pettus <xof@thebuild.com>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Christophe Pettus @ 2021-05-26 00:27 UTC (permalink / raw)
To: pgsql-performance; +Cc: Nagaraj Raj <nagaraj.sf@yahoo.com>; David Rowley <dgrowleyml@gmail.com>
> 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, but excluding the partitioned table from the JOINs for the branch of the UNION that doesn't appear to actually require them.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-05-26 00:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-25 22:50 issue partition scan Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-05-25 23:01 ` Christophe Pettus <xof@thebuild.com>
2021-05-25 23:38 ` Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-05-26 00:16 ` David Rowley <dgrowleyml@gmail.com>
2021-05-26 00:27 ` Christophe Pettus <xof@thebuild.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox