Re: *Regarding brin_index on required column of the table - Mailing list pgsql-general

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