Your plan is not readable to me (perhaps because of gmail). Does https://explain.depesz.com/ give you any useful insights? On Fri, 21 Sep 2018 at 16:15, Durgamahesh Manne <maheshpostgr...@gmail.com> wrote: > > > On Fri, Sep 21, 2018 at 7:38 PM Durgamahesh Manne <maheshpostgr...@gmail.com> > wrote: >> >> Hi >> >> Complex query taken around 30 minutes to execute even i have increased >> work_mem value to 4GB temporarily as total ram is 16gb >> >> Explain analyze query taken around 30 minutes to execute even i have created >> partial indexes with where condition on required columns >> >> >> >> Below is the query plan for explain analyze query : >> >> | HashAggregate (cost=16034967.98..16035010.75 rows=4277 width=1710) >> (actual time=1806653.536..1806680.802 rows=26098 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Group Key: (max((v."vchSubmittersCode")::text)), >> (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), >> v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", >> (max((v."vchPartyNatural_Non_NaturalEntity")::text)), >> (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), >> (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), >> (max((v."vchPartySuffix")::text)), (NULL::text), v."vchPartyID", >> (max((v."vchPartyIDQualifier")::text)), >> (max((v."vchTrustRevocabilityIndicator")::text)), (NULL::text), >> (NULL::text), (max((v."vchFiller1")::text)), >> (max((v."vchRejectCode")::text)), >> (max((v."vchContractEntityAddressLine1")::text)), >> (max((v."vchContractEntityAddressLine2")::text)), >> (max((v."vchContractEntityCity")::text)), >> (max((v."vchContractEntityState")::text)), >> (max((v."vchContractEntityZip")::text)), >> (max((v."vchContractEntityAddressLine3")::text)), >> (max((v."vchContractEntityAddressLine4")::text)), >> (max((v."vchContractEntityAddressLine5")::text)), >> (max((v."vchPartyDateofBirth")::text)), >> (max((v."vchPartyAddressLine1")::text)), >> (max((v."vchContractStatus")::text)), (string_agg(DISTINCT >> btrim((s."vchAgentTaxID")::text), ','::text)), v."vchPartyRole", >> (max((v."vchAdvisorLabel")::text)), v."vchFileName", >> (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), >> (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT >> ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || >> btrim((s."vchAgentMiddleName")::text)) || ' '::text) || >> btrim((s."vchAgentLastName")::text)), ','::text)) | >> | -> Append (cost=48944.67..16034550.97 rows=4277 width=1710) (actual >> time=3324.233..1806605.691 rows=26098 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Unique (cost=48944.67..49103.43 rows=1764 width=1145) (actual >> time=3324.233..3327.824 rows=3637 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Sort (cost=48944.67..48949.08 rows=1764 width=1145) >> (actual time=3324.232..3324.447 rows=3637 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Sort Key: (max((v."vchSubmittersCode")::text)), >> (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), >> v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", >> (max((v."vchPartyNatural_Non_NaturalEntity")::text)), >> (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), >> (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), >> (max((v."vchPartySuffix")::text)), v."vchPartyID", >> (max((v."vchPartyIDQualifier")::text)), >> (max((v."vchTrustRevocabilityIndicator")::text)), >> (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), >> (max((v."vchContractEntityAddressLine1")::text)), >> (max((v."vchContractEntityAddressLine2")::text)), >> (max((v."vchContractEntityCity")::text)), >> (max((v."vchContractEntityState")::text)), >> (max((v."vchContractEntityZip")::text)), >> (max((v."vchContractEntityAddressLine3")::text)), >> (max((v."vchContractEntityAddressLine4")::text)), >> (max((v."vchContractEntityAddressLine5")::text)), >> (max((v."vchPartyDateofBirth")::text)), >> (max((v."vchPartyAddressLine1")::text)), >> (max((v."vchContractStatus")::text)), (string_agg(DISTINCT >> btrim((s."vchAgentTaxID")::text), ','::text)), >> (max((v."vchAdvisorLabel")::text)), v."vchFileName", >> (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), >> (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT >> ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || >> btrim((s."vchAgentMiddleName")::text)) || ' '::text) || >> btrim((s."vchAgentLastName")::text)), ','::text)) >> | >> | Sort Method: quicksort Memory: 3366kB >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> GroupAggregate (cost=26241.74..48849.55 rows=1764 >> width=1145) (actual time=1451.987..3297.428 rows=3637 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Group Key: v."vchPartyRole", v."vchFileName", >> v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Sort (cost=26241.74..26754.95 rows=205285 >> width=709) (actual time=1451.641..1474.286 rows=232227 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Sort Key: v."vchPartyRole", >> v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", >> v."vchContractNumber" >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Sort Method: quicksort Memory: 241964kB >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Merge Join (cost=23.03..8128.14 >> rows=205285 width=709) (actual time=0.072..220.689 rows=232227 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Merge Cond: >> ((v."vchContractNumber")::text = (s."vchContractNumber")::text) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Index Scan using cpr_idx1 on >> "table1" v (cost=0.28..221.46 rows=4200 width=602) (actual >> time=0.030..3.283 rows=4200 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Materialize (cost=0.42..5130.71 >> rows=125522 width=138) (actual time=0.007..116.095 rows=249620 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Index Scan using cpr_idx2 >> on "table2" s (cost=0.42..4816.91 rows=125522 width=138) (actual >> time=0.005..81.432 rows=125522 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Subquery Scan on "*SELECT* 2" (cost=15985197.45..15985429.90 >> rows=2513 width=1180) (actual time=1803251.051..1803276.051 rows=22461 >> loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Unique (cost=15985197.45..15985404.77 rows=2513 >> width=1180) (actual time=1803251.046..1803266.512 rows=22461 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Sort (cost=15985197.45..15985203.73 rows=2513 >> width=1180) (actual time=1803251.043..1803253.167 rows=22461 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Sort Key: (max((j."vchSubmittersCode")::text)), >> (max((j."vchRecordType")::text)), (max((j."vchSequenceNumber")::text)), >> j."vchContractNumber", (max((j."vchContractEntityTypeCode")::text)), >> j."vchContractEntityRole", >> (max((j."vchContractEntityNatural_Non_NaturalNameIndicator")::text)), >> (max((j."vchContractEntityLastName")::text)), >> (max((j."vchContractEntityFirstName")::text)), >> (max((j."vchContractEntityMiddleName")::text)), >> (max((j."vchContractEntityPrefix")::text)), >> (max((j."vchContractEntitySuffix")::text)), >> (max((j."vchContractEntityE_mailAddress")::text)), >> j."vchContractEntityPersonalIdentifier", >> (max((j."vchContractEntityPersonalQualifier")::text)), >> (max((j."vchTrustRevocabilityIndicator")::text)), >> (max((j."vchContractEntityPhoneNumber")::text)), >> (max((j."vchContractEntityPhoneExtension")::text)), >> (max((j."vchFiller1")::text)), (max((j."vchRejectCode")::text)), >> (max((j.vchcontractentityaddressline1)::text)), >> (max((j.vchcontractentityaddressline2)::text)), >> (max((j.vchcontractentitycity)::text)), >> (max((j.vchcontractentitystate)::text)), >> (max((j.vchcontractentityzip)::text)), >> (max((j.vchcontractentityaddressline3)::text)), >> (max((j.vchcontractentityaddressline4)::text)), >> (max((j.vchcontractentityaddressline5)::text)), (string_agg(DISTINCT >> btrim((j.vchagenttaxid)::text), ','::text)), >> (max((j."vchAdvisorLabel")::text)), j."vchFileName", >> (((((btrim(max((k."vchAgentFirstName")::text)) || ' '::text) || >> btrim(max((k."vchAgentMiddleName")::text))) || ' '::text) || >> btrim(max((k."vchAgentLastName")::text)))) | >> | Sort Method: quicksort Memory: 23482kB >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> GroupAggregate >> (cost=10588651.59..15985055.52 rows=2513 width=1180) (actual >> time=847482.207..1802617.045 rows=22461 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Group Key: j."vchFileName", >> j."vchContractEntityRole", j."vchContractNumber", >> j."vchContractEntityPersonalIdentifier" >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Sort (cost=10588651.59..10738549.61 >> rows=59959206 width=677) (actual time=847464.789..1486679.680 rows=61595746 >> loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Sort Key: j."vchFileName", >> j."vchContractEntityRole", j."vchContractNumber", >> j."vchContractEntityPersonalIdentifier" >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Sort Method: external merge Disk: >> 42758304kB >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Nested Loop >> (cost=0.42..266305.78 rows=59959206 width=677) (actual time=0.122..73786.837 >> rows=61595746 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Seq Scan on "table3" j >> (cost=0.00..669.12 rows=25132 width=591) (actual time=0.021..28.338 >> rows=25132 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Filter: (NOT "bFetch") >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | -> Index Scan using cpr_idx4 >> on table2 k (cost=0.42..6.92 rows=365 width=107) (actual time=0.838..2.244 >> rows=2451 loops=25132) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Index Cond: >> (("vchAgentTaxID")::text = (j.vchagenttaxid)::text) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Planning time: 2.369 ms >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Execution time: 1807771.091 ms >> >> >> So i need to execute below query at less time. please help in >> optimising the complex query execution time >> >> >> Regards >> >> Durgamahesh Manne > > > So i need to execute below query at less time as i just sent query plan to > mailing list > please help in optimising the complex query execution time > > 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"; >
-- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
