From: Nagaraj Raj <nagaraj.sf@yahoo.com>
To: Pgsql-performance <pgsql-performance@postgresql.org>
Subject: issue partition scan
Date: Tue, 25 May 2021 22:50:48 +0000 (UTC)
Message-ID: <44089786.116151.1621983048012@mail.yahoo.com> (raw)
References: <44089786.116151.1621983048012.ref@mail.yahoo.com>
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
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
Subject: Re: issue partition scan
In-Reply-To: <44089786.116151.1621983048012@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