Обсуждение: Regarding query execution for long time

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))

The query optimizer probably can't use indexes on those tables.


-- 
Angular momentum makes the world go 'round.

On 1/28/19 8:10 AM, Durgamahesh Manne wrote:

On Mon, Jan 28, 2019 at 6:34 PM Ron <ronljohnsonjr@gmail.com> 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))

The query optimizer probably can't use indexes on those tables.

Hi 

 

  why query optimizer can't use indexes on those columns of the tables

Because of the ltrim() functions.  B-trees sort the data on the field values, and ltrim() changes that.
For example, these two strings are different, and therefore the b-tree puts them in different places in the tree.
  snagglefrob
snagglefrob

Using lrtrim() makes them logically eqivalent, but not physically equivalent.

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.klaver@aklaver.com

On 1/28/19 4: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

See below for information on how to report these sort of issues:

https://wiki.postgresql.org/wiki/SlowQueryQuestions

> 
> Regards
> durgamahesh manne
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com