From: | Christophe Pettus <xof(at)thebuild(dot)com> |
---|---|
To: | Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> |
Cc: | Pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: issue partition scan |
Date: | 2021-05-25 23:01:39 |
Message-ID: | 366856CE-2043-4163-9753-A735B02E81EA@thebuild.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Nagaraj Raj | 2021-05-25 23:38:04 | Re: issue partition scan |
Previous Message | Nagaraj Raj | 2021-05-25 22:50:48 | issue partition scan |