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

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
<maheshpostgres9@gmail.com> wrote:
>
>
> On Fri, Sep 21, 2018 at 7:38 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
>>
>> Hi
>>
>> Complex query taken around 30 minutes to execute even i have increased work_mem value to 4GB  temporarily as total
ramis 16gb 
>>
>> Explain analyze query taken around 30 minutes to execute even i have created partial indexes with where condition on
requiredcolumns 
>>
>>
>>
>> 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=26098loops=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(DISTINCTbtrim((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.428rows=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.286rows=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.689rows=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.095rows=249620 loops=1)











          | 
>> |                                             ->  Index Scan using cpr_idx2 on "table2" s  (cost=0.42..4816.91
rows=125522width=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.051rows=22461 loops=1)











            | 
>> |               ->  Unique  (cost=15985197.45..15985404.77 rows=2513 width=1180) (actual
time=1803251.046..1803266.512rows=22461 loops=1)











                             | 
>> |                     ->  Sort  (cost=15985197.45..15985203.73 rows=2513 width=1180) (actual
time=1803251.043..1803253.167rows=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.045rows=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.680rows=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.837rows=61595746 loops=1)











      | 
>> |                                             ->  Seq Scan on "table3" j  (cost=0.00..669.12 rows=25132 width=591)
(actualtime=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")
asvchPartyLastName ,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
ASs on v."vchContractNumber" = s."vchContractNumber" where v."bFetch" = false  GROUP BY
"vchPartyRole",v."vchFileName","vchPartyID","vchPartyRole" ,"vchContractPartyRoleQualifier" , v."vchContractNumber"
UNIONSELECT 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
GROUPBY 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.