Re: *Regarding brin_index on required column of the table

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