Re: *Regarding brin_index on required column of the table

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