From: | Durgamahesh Manne <maheshpostgres9(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Ron <ronljohnsonjr(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Regarding query execution for long time |
Date: | 2019-01-28 15:20:12 |
Message-ID: | CAJCZkoL2b_+Uy_JU1if=iGpRNCoML5qstuYjAFCkyZ4--RNCig@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Jan 28, 2019 at 8:41 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> On 1/28/19 5:04 AM, Ron wrote:
> > On 1/28/19 6:20 AM, Durgamahesh Manne wrote:
> >> Hi
> >>
> >> below query is being executed for long time
> >>
> >> Select
> >> distinct ltrim(rtrim(ssnumber)), CL.iInsightClientId,
> >> ltrim(rtrim(TFA.client_account_key)) as vchAccountNumber,
> >> concat(TFA.first_name, ' ', TFA.last_name) as vchAccountName,
> >> concat('AP:TIAA', TFA.routing_number) as vchRepLabel,
> >> null as vchOpenDate, TFA.address1 as vchAccountTiteLine2,
> >> TFA.address2 as vchAccountTitleLine3,null as vchAccountType,
> >> TFA.address1 as vchPrimaryContact, 'TIAA-CREF' as Custodian,
> >> TFA.routing_number as vchCustodian_RepId, null as vchCustodianRepName,
> >> CONCAT ('TIAA-CREF:',ltrim(rtrim(client_account_key)),':',
> >> ltrim(rtrim(ssnumber))) as vchAccountKey,
> >> null as vchFeedsAccountType
> >> from accounts as TFA
> >> join client3 as CL on
> >> ltrim(rtrim(Cl.vchssnumber))=ltrim(rtrim(TFA.ssnumber))
> >> left join account3 as AC on
> >>
> ltrim(rtrim(AC."vchCustodianAccountNumber"))=ltrim(rtrim(TFA.client_account_key))
>
> >>
> >> where AC."iInsightAccountID" is null;
> >>
> >> query is being executed for long time even after i have created
> >> required indexes on columns of the tables
> >> please help for fast query execution
> >
> > I bet this is what's causing your troubles:
> > on ltrim(rtrim(Cl.vchssnumber))=ltrim(rtrim(TFA.ssnumber))
>
> There is also the question of why not do?:
>
> btrim(Cl.vchssnumber) = btrim(TFA.ssnumber)
>
> >
> > The query optimizer probably can't use indexes on those tables.
> >
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>
hi
CREATE INDEX idx10 on account3 USING btree
(ltrim(rtrim("vchCustodianAccountNumber")));
CREATE INDEX idx11 on accounts USING btree
(ltrim(rtrim(client_account_key)));
CREATE INDEX idx12 on accounts USING btree (ltrim(rtrim(ssnumber)));
CREATE INDEX idx13 on client3 USING btree (ltrim(rtrim(vchssnumber)));
after i have created indexes on columns by adding the ltrim rtrim functions
query took very less to execution
Regards
durgamahesh manne
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-01-28 15:27:58 | Re: Displaying Comments in Views |
Previous Message | Adrian Klaver | 2019-01-28 15:16:37 | Re: Regarding query execution for long time |