From: Nagaraj Raj <nagaraj.sf@yahoo.com>
To: Christophe Pettus <xof@thebuild.com>
Cc: Pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: issue partition scan
Date: Tue, 25 May 2021 23:38:04 +0000 (UTC)
Message-ID: <267690084.131091.1621985884945@mail.yahoo.com> (raw)
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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nagaraj.sf@yahoo.com, xof@thebuild.com
Subject: Re: issue partition scan
In-Reply-To: <267690084.131091.1621985884945@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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