issue partition scan

From: Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com>
To: Pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: issue partition scan
Date: 2021-05-25 22:50:48
Message-ID: 44089786.116151.1621983048012@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Christophe Pettus 2021-05-25 23:01:39 Re: issue partition scan
Previous Message Alexey M Boltenkov 2021-05-24 21:59:11 Re: transaction blocking on COMMIT