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

--
Angular momentum makes the world go 'round.

Hi

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

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

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

--
Angular momentum makes the world go 'round.

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

On Mon, Jan 28, 2019 at 8:41 PM Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.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