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
