From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | 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:11:17 |
Message-ID: | 80f20455-11ca-792b-f145-9f3979a851f8@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-01-28 15:16:37 | Re: Regarding query execution for long time |
Previous Message | Susan Hurst | 2019-01-28 15:08:13 | Displaying Comments in Views |