Am 21.09.2018 um 17:49 schrieb Durgamahesh Manne:
Please find below attached query plan file
query and plan still hard to read :-(
Query:
SELECT distinct
Max(v."vchSubmittersCode") as vchSubmittersCode
, Max(v."vchRecordType") as vchRecordType
, Max(v."vchSequenceNumber") as vchSequenceNumber
, v."vchContractNumber"
,"vchContractPartyRoleQualifier"
,"vchPartyRole"
,Max("vchPartyNatural_Non_NaturalEntity") as
vchPartyNatural_Non_NaturalEntity
, Max("vchPartyLastName") as vchPartyLastName
,Max("vchPartyFirstName") as vchPartyFirstName
,Max("vchPartyMiddleName") as vchPartyMiddleName
, Max("vchPartyPrefix") as vchPartyPrefix
,Max("vchPartySuffix") as vchPartySuffix
, NULL "vchContractEntityE_mailAddress"
, "vchPartyID"
, Max("vchPartyIDQualifier") as vchPartyIDQualifier
,Max("vchTrustRevocabilityIndicator") as vchTrustRevocabilityIndicator
,NULL "vchContractEntityPhoneNumber"
,NULL "vchContractEntityPhoneExtension"
,Max(v."vchFiller1") as vchFiller1
,Max(v."vchRejectCode") as vchRejectCode
, Max("vchContractEntityAddressLine1") as vchContractEntityAddressLine1
, Max("vchContractEntityAddressLine2") as vchContractEntityAddressLine2
, Max("vchContractEntityCity") as vchContractEntityCity
, Max("vchContractEntityState") as vchContractEntityState
, Max("vchContractEntityZip") as vchContractEntityZip
, Max("vchContractEntityAddressLine3") as vchContractEntityAddressLine3
, Max("vchContractEntityAddressLine4") as vchContractEntityAddressLine4
, Max("vchContractEntityAddressLine5") as vchContractEntityAddressLine5
,Max("vchPartyDateofBirth") as vchPartyDateofBirth
, Max("vchPartyAddressLine1") as vchPartyAddressLine1
, Max("vchContractStatus") as vchContractStatus
, string_agg(distinct trim(s."vchAgentTaxID"),',') as vchAgentTaxID
, "vchPartyRole"
,Max(v."vchAdvisorLabel") as vchAdvisorLabel
,v."vchFileName"
,Max("vchpartycity") as vchpartycity
,Max("vchpartystate") as vchpartystate
,Max("vchpartypostalcode") as vchpartypostalcode
,string_agg(distinct trim(s."vchAgentFirstName")||'
'||trim(s."vchAgentMiddleName")||' '||trim(s."vchAgentLastName"),',') as
"AgentName"
FROM
TABLE1 as v
join"DTCC".TABLE2 AS s on v."vchContractNumber" = s."vchContractNumber"
where v."bFetch" = false
GROUP BY
"vchPartyRole"
,v."vchFileName"
,"vchPartyID"
,"vchPartyRole"
,"vchContractPartyRoleQualifier"
, v."vchContractNumber"
UNION SELECT distinct
max(j."vchSubmittersCode") as vchSubmittersCode
,max(j."vchRecordType") as vchRecordType
,max(j."vchSequenceNumber") as vchSequenceNumber
, j."vchContractNumber"
, max("vchContractEntityTypeCode") as vchContractEntityTypeCode
,"vchContractEntityRole"
,max("vchContractEntityNatural_Non_NaturalNameIndicator") as
vchContractEntityNatural_Non_NaturalNameIndicator
,max("vchContractEntityLastName") as vchContractEntityLastName
, max("vchContractEntityFirstName") as vchContractEntityFirstName
, max("vchContractEntityMiddleName") as vchContractEntityMiddleName
, max("vchContractEntityPrefix") as vchContractEntityPrefix
, max("vchContractEntitySuffix") as vchContractEntitySuffix
, max("vchContractEntityE_mailAddress") as
vchContractEntityE_mailAddress
, "vchContractEntityPersonalIdentifier"
, max("vchContractEntityPersonalQualifier") as
vchContractEntityPersonalQualifier
, max("vchTrustRevocabilityIndicator") as vchTrustRevocabilityIndicator
, max("vchContractEntityPhoneNumber") as vchContractEntityPhoneNumber
, max("vchContractEntityPhoneExtension") as
vchContractEntityPhoneExtension
, max(j."vchFiller1") as vchFiller1
, max(j."vchRejectCode") as vchRejectCode
, max("vchcontractentityaddressline1") as vchcontractentityaddressline1
,max("vchcontractentityaddressline2") as vchcontractentityaddressline2
,max("vchcontractentitycity") as vchcontractentitycity
, max("vchcontractentitystate") as vchcontractentitystate
,max("vchcontractentityzip") as vchcontractentityzip
, max("vchcontractentityaddressline3") as vchcontractentityaddressline3
,max("vchcontractentityaddressline4") as vchcontractentityaddressline4
,max("vchcontractentityaddressline5") as vchcontractentityaddressline5
, NULL "vchPartyDateofBirth"
, NULL "vchPartyAddressLine1"
, NULL "vchContractStatus"
, string_agg(distinct trim(j."vchagenttaxid"),',') as vchagenttaxid
, "vchContractEntityRole"
, max(j."vchAdvisorLabel") as vchAdvisorLabel
,j."vchFileName"
,NULL "vchpartycity"
, NULL "vchpartystate"
,NULL "vchpartypostalcode"
, trim(max(k."vchAgentFirstName"))||'
'||trim(max(k."vchAgentMiddleName"))||'
'||trim(max(k."vchAgentLastName")) as "AgentName"
FROM TABLE3 as j
join TABLE2 AS k on j."vchagenttaxid" = k."vchAgentTaxID"
where j."bFetch" = false
GROUP BY
j."vchFileName"
,"vchContractEntityRole"
, "vchContractEntityRole"
,j."vchContractNumber"
,"vchContractEntityPersonalIdentifier"
;
i can see a lot of max(string-field) (for instance, LastName,
MiddleName, FirstName).
wild guess: completely broken design, but i don't know your application
and use-case for that.
again, as i said already, i think this is a case for an in-deep
consultation.
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
