explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HN4F : test100

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 22,365.961 ↑ 12.0 1 1

Sort (cost=3,984.23..3,984.26 rows=12 width=81) (actual time=22,365.961..22,365.961 rows=1 loops=1)

  • Output: "PC0".pyworkbasket, "PC0".pyworkgroup, "PC0".pylabel, (count("WBCC".pylabel)), (count("AssignWB".pxcreatedatetime)), (count("AssignWB".pxdeadlineexecute)), (count("AssignWB".pxgoalexecute))
  • Sort Key: "PC0".pylabel, (count("AssignWB".pxcreatedatetime)) DESC
  • Sort Method: quicksort Memory: 25kB
  • "NNUW".pylabel, "NNUW".pyorigdivision, "NNUW".pyorigorg, "NNUW".pyorigorgunit, "NNUW".pyoriguserdivision, "NNUW".pyoriguserid, "NNUW".pyoriguserworkgroup, "NNUW".pyownerdivision, "NNUW".pyownerorg, "NNUW".pyownerorgunit, "NNUW".pyprimarycontact, "NNUW".pyproblemreason, "NNUW".pyproblemsource, "NNUW".pyproblemtype, "NNUW".pyreopencount, "NNUW".pyreopentimestamp, "NNUW".pyresolutioncomplexity, "NNUW".pyresolutioncost, "NNUW".pyresolveddivision, "NNUW".pyresolvedorg, "NNUW".pyresolvedorgunit, "NNUW".pyresolvedtime, "NNUW".pyresolvedtimestamp, "NNUW".pyresolveduserid, "NNUW".pyresolveduserworkgroup, "NNUW".pyrootcause, "NNUW".pysladeadline, "NNUW".pyslagoal, "NNUW".pyslaname, "NNUW".pystatuscustomersat, "NNUW".pystatuswork, "NNUW".pyworklistdate1, "NNUW".pyworklistdatetime1, "NNUW".pyworklistdatetime2, "NNUW".pyworklistdecimal1, "NNUW".pyworklistdecimal2, "NNUW".pyworklistinteger1, "NNUW".pyworklisttext1, "NNUW".pyworklisttext2, "NNUW".pyworklisttext3, "NNUW".pxcurrentstagelabel, "NNUW".pxcurrentstage, "NNUW".pzinskey, "NNUW".pzpvstream, "NNUW".taxidnumber_1, "NNUW".groupnumber_1, "NNUW".state_1, "NNUW".writingnumber_1,
  • "NNUW".internetrequest_1, "NNUW".type_1, "NNUW".worktype_1, "NNUW".receivedtimestamp_1, "NNUW".origin_1, "NNUW".source, "NNUW".sicrecord_1, "NNUW".sicrecord_2, "NNUW".companycode_1, "NNUW".companycode_2, "NNUW".policyno_1, "NNUW".producedstate_1, "NNUW".function_1, "NNUW".lob_1, "NNUW".batch_1, "NNUW".origscan_1, "NNUW".karzcode_1, "NNUW".batchnumber_1, "NNUW".docclass_1, "NNUW".requesttype_1, "NNUW".applicantssn_1, "NNUW".applicantlastname_1, "NNUW".applicantfirstname_1, "NNUW".applicantdob_1, "NNUW".zip_1, "NNUW".trainerroute, "NNUW".filestatus_1, "NNUW".origscandatetime_1, "NNUW".requesttype_2, "NNUW".totalamtcoverage_1, "NNUW".faxindate_1, "NNUW".billingstatus_1, "NNUW".routeto, "NNUW".groupname_1, "NNUW".rtninterview_1, "NNUW".pendedbusinessissue_1, "NNUW".newaccountresponse, "NNUW".coverageamountapplied_1, "NNUW".decisionbasedon, "NNUW".uwdecision, "NNUW".uwdecisionprimaryreason, "NNUW".uwdecisionmib, "NNUW".uwdecisionspousecoverage, "NNUW".uwdecisionotherreason, "NNUW".uwapplicantage, "NNUW".applicationpencode, "NNUW".userroutedto
2. 0.221 22,365.956 ↑ 12.0 1 1

GroupAggregate (cost=3,983.66..3,984.02 rows=12 width=81) (actual time=22,365.956..22,365.956 rows=1 loops=1)

  • Output: "PC0".pyworkbasket, "PC0".pyworkgroup, "PC0".pylabel, (count("WBCC".pylabel)), count("AssignWB".pxcreatedatetime), count("AssignWB".pxdeadlineexecute), count("AssignWB".pxgoalexecute)
  • Group Key: "PC0".pyworkbasket, "PC0".pyworkgroup, "PC0".pylabel, (count("WBCC".pylabel))
3. 3.793 22,365.735 ↓ 101.8 1,222 1

Sort (cost=3,983.66..3,983.69 rows=12 width=81) (actual time=22,365.681..22,365.735 rows=1,222 loops=1)

  • Output: "PC0".pyworkbasket, "PC0".pyworkgroup, "PC0".pylabel, (count("WBCC".pylabel)), "AssignWB".pxcreatedatetime, "AssignWB".pxdeadlineexecute, "AssignWB".pxgoalexecute
  • Sort Key: "PC0".pylabel, (count("WBCC".pylabel))
  • Sort Method: quicksort Memory: 220kB
4. 4.727 22,361.942 ↓ 101.8 1,222 1

Nested Loop Left Join (cost=1,020.16..3,983.44 rows=12 width=81) (actual time=16.725..22,361.942 rows=1,222 loops=1)

  • Output: "PC0".pyworkbasket, "PC0".pyworkgroup, "PC0".pylabel, (count("WBCC".pylabel)), "AssignWB".pxcreatedatetime, "AssignWB".pxdeadlineexecute, "AssignWB".pxgoalexecute
  • Inner Unique: true
  • Join Filter: ((("PC0".pxobjclass)::text = 'Data-Admin-WorkBasket'::text) AND (("WBCC".pxassignedoperatorid)::text = ("PC0".pyworkbasket)::text))
5. 1.690 6.835 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=0.70..7.21 rows=1 width=98) (actual time=0.034..6.835 rows=1,222 loops=1)

  • Output: "PC0".pyworkbasket, "PC0".pyworkgroup, "PC0".pylabel, "PC0".pxobjclass, "AssignWB".pxcreatedatetime, "AssignWB".pxdeadlineexecute, "AssignWB".pxgoalexecute
  • Join Filter: ((("PC0".pxobjclass)::text = 'Data-Admin-WorkBasket'::text) AND (("PC0".pyworkbasket)::text = ("AssignWB".pxassignedoperatorid)::text))
6. 0.012 0.012 ↑ 1.0 1 1

Index Scan using pr_data_admin_idx3 on pegadata.pr_data_admin "PC0" (cost=0.28..2.30 rows=1 width=74) (actual time=0.011..0.012 rows=1 loops=1)

  • Output: "PC0".pxcommitdatetime, "PC0".pxsavedatetime, "PC0".pxcreatedatetime, "PC0".pxcreateopname, "PC0".pxcreateoperator, "PC0".pxcreatesystemid, "PC0".pxinsname, "PC0".pxobjclass, "PC0".pxproductname, "PC0".pxproductpatchversion, "PC0".pxproductversion, "PC0".pxupdatedatetime, "PC0".pxupdateopname, "PC0".pxupdateoperator, "PC0".pxupdatesystemid, "PC0".pyaccessgroup, "PC0".pydefaultappname, "PC0".pydefaultappversion, "PC0".pyexpiration, "PC0".pylabel, "PC0".pymanager, "PC0".pyname, "PC0".pyorgdivision, "PC0".pyorgunit, "PC0".pyorganization, "PC0".pyowner, "PC0".pypurpose, "PC0".pyrulesetname, "PC0".pysystemname, "PC0".pytimestamp, "PC0".pyworkbasket, "PC0".pyworkgroup, "PC0".pyworkgroupname, "PC0".pyworkpool, "PC0".pysetting, "PC0".pynodename, "PC0".pxsystemnamesetfromfile, "PC0".pzinskey, "PC0".pzpvstream, "PC0".pyportal, "PC0".pykeystore, "PC0".pyprocessorname, "PC0".pyprofilename, "PC0".pyservicepackage, "PC0".pypolicyprofilename, "PC0".pyworkbaskettype, "PC0".pymaxworkbasketsize, "PC0".pymaxexecutiontime, "PC0".pygranttype_1, "PC0".pyauthenticationscheme
  • Index Cond: (("PC0".pyworkbasket)::text = 'DestinationCode'::text)
  • Filter: ((("PC0".pxobjclass)::text = 'Data-Admin-WorkBasket'::text) AND (("PC0".pyworkgroup)::text = 'NewBusiness'::text))
7. 5.133 5.133 ↓ 611.0 1,222 1

Index Scan using assign_icgetwb_refwgapp on pegadata.pc_assign_workbasket "AssignWB" (cost=0.42..4.88 rows=2 width=35) (actual time=0.021..5.133 rows=1,222 loops=1)

  • Output: "AssignWB".pxcommitdatetime, "AssignWB".pxsavedatetime, "AssignWB".pxapplication, "AssignWB".pxassignedoperatorid, "AssignWB".pxassignedorg, "AssignWB".pxassignedorgdiv, "AssignWB".pxassignedorgunit, "AssignWB".pxcreatedatetime, "AssignWB".pxcreateopname, "AssignWB".pxcreateoperator, "AssignWB".pxcreatesystemid, "AssignWB".pxdeadlineexecute, "AssignWB".pxdeadlinetime, "AssignWB".pxflowinskey, "AssignWB".pxflowname, "AssignWB".pxgoalexecute, "AssignWB".pxgoaltime, "AssignWB".pxinsname, "AssignWB".pxlatetime, "AssignWB".pxobjclass, "AssignWB".pxrefobjectclass, "AssignWB".pxrefobjectinsname, "AssignWB".pxrefobjectkey, "AssignWB".pxrefqueuekey, "AssignWB".pxservicelevelname, "AssignWB".pxsystemflow, "AssignWB".pxtasklabel, "AssignWB".pxtaskname, "AssignWB".pxtimeflowstarted, "AssignWB".pxupdatedatetime, "AssignWB".pxupdateopname, "AssignWB".pxupdateoperator, "AssignWB".pxupdatesystemid, "AssignWB".pxurgency, "AssignWB".pxurgencyassign, "AssignWB".pxworkgroup, "AssignWB".pyactiontime, "AssignWB".pyassignmentstatus, "AssignWB".pyeffortactual, "AssignWB".pyerrorassignment, "AssignWB".pyerrormessage, "AssignWB".pyerrorstep, "AssignWB".pyflowinerror, "AssignWB".pyflowtype, "AssignWB".pyinstructions, "AssignWB".pyinterestpageclass, "AssignWB".pylabel, "AssignWB".pyprocessonnode, "AssignWB".pyresolutioncost, "AssignWB".pxstagelabel, "AssignWB".pxstageid, "AssignWB".pzinskey, "AssignWB".pzpvstream, "AssignWB".pxrequiredskillscount, "AssignWB".pxapplicationversion
  • Index Cond: ((("AssignWB".pxassignedoperatorid)::text = 'DestinationCode'::text) AND (("AssignWB".pxworkgroup)::text = 'NewBusiness'::text) AND (("AssignWB".pxapplication)::text = ANY ('{NBUS,NNU-Underwriting}'::text[])))
  • Filter: (("AssignWB".pxobjclass)::text = 'Assign-WorkBasket'::text)
8. 3.666 22,350.380 ↑ 94.0 1 1,222

Finalize GroupAggregate (cost=1,019.46..3,973.88 rows=94 width=19) (actual time=18.290..18.290 rows=1 loops=1,222)

  • Output: "WBCC".pxassignedoperatorid, count("WBCC".pylabel)
  • Group Key: "WBCC".pxassignedoperatorid
9. 3,480.256 22,346.714 ↑ 47.0 2 1,222

Gather (cost=1,019.46..3,972.47 rows=94 width=19) (actual time=18.200..18.287 rows=2 loops=1,222)

  • Output: "WBCC".pxassignedoperatorid, (PARTIAL count("WBCC".pylabel))
  • Workers Planned: 1
  • Workers Launched: 1
10. 103.870 18,866.458 ↑ 94.0 1 2,444 / 2

Partial GroupAggregate (cost=19.46..2,963.07 rows=94 width=19) (actual time=15.439..15.439 rows=1 loops=2,444)

  • Output: "WBCC".pxassignedoperatorid, PARTIAL count("WBCC".pylabel)
  • Group Key: "WBCC".pxassignedoperatorid
  • Worker 0: actual time=13.835..13.835 rows=1 loops=1,222
11. 795.522 18,762.588 ↑ 1.1 606 2,444 / 2

Nested Loop (cost=19.46..2,958.66 rows=694 width=17) (actual time=0.243..15.354 rows=606 loops=2,444)

  • Output: "WBCC".pxassignedoperatorid, "WBCC".pylabel
  • Inner Unique: true
  • Worker 0: actual time=0.069..13.758 rows=541 loops=1,222
12. 1,410.188 1,540.942 ↑ 1.2 611 2,444 / 2

Parallel Bitmap Heap Scan on pegadata.pc_assign_workbasket "WBCC" (cost=19.04..1,221.99 rows=730 width=46) (actual time=0.203..1.261 rows=611 loops=2,444)

  • Output: "WBCC".pxcommitdatetime, "WBCC".pxsavedatetime, "WBCC".pxapplication, "WBCC".pxassignedoperatorid, "WBCC".pxassignedorg, "WBCC".pxassignedorgdiv, "WBCC".pxassignedorgunit, "WBCC".pxcreatedatetime, "WBCC".pxcreateopname, "WBCC".pxcreateoperator, "WBCC".pxcreatesystemid, "WBCC".pxdeadlineexecute, "WBCC".pxdeadlinetime,"WBCC".pxflowinskey, "WBCC".pxflowname, "WBCC".pxgoalexecute, "WBCC".pxgoaltime, "WBCC".pxinsname, "WBCC".pxlatetime, "WBCC".pxobjclass, "WBCC".pxrefobjectclass, "WBCC".pxrefobjectinsname,WBCC".pxrefobjectkey, "WBCC".pxrefqueuekey, "WBCC".pxservicelevelname, "WBCC".pxsystemflow, "WBCC".pxtasklabel, "WBCC".pxtaskname, "WBCC".pxtimeflowstarted, "WBCC".pxupdatedatetime, "WBCC.pxupdateopname, "WBCC".pxupdateoperator, "WBCC".pxupdatesystemid, "WBCC".pxurgency, "WBCC".pxurgencyassign, "WBCC".pxworkgroup, "WBCC".pyactiontime, "WBCC".pyassignmentstatus, "WBCC".pyeffortactual, "WBCC".pyerrorassignment, "WBCC".pyerrormessage, "WBCC".pyerrorstep, "WBCC".pyflowinerror, "WBCC".pyflowtype, "WBCC".pyinstructions, "WBCC".pyinterestpageclass, "WBCC".pylabel, "WBCC".pyprocessonnode, "WBCC".pyresolutioncost, "WBCC".pxstagelabel, "WBCC".pxstageid, "WBCC".pzinskey, "WBCC".pzpvstream, "WBCC".pxrequiredskillscount, "WBCC".pxapplicationversion
  • Recheck Cond: (("WBCC".pxassignedoperatorid)::text = 'DestinationCode'::text)
  • Filter: (("WBCC".pxobjclass)::text = 'Assign-WorkBasket'::text)
  • Heap Blocks: exact=777,676
  • Worker 0: actual time=0.029..1.027 rows=545 loops=1,222
13. 130.754 130.754 ↑ 1.0 1,222 1,222 / 2

Bitmap Index Scan on pxassignedoperatorid_idx (cost=0.00..18.72 rows=1,241 width=0) (actual time=0.214..0.214 rows=1,222 loops=1,222)

  • Index Cond: (("WBCC".pxassignedoperatorid)::text = 'DestinationCode'::text)
14. 16,426.124 16,426.124 ↑ 1.0 1 1,493,284 / 2

Index Scan using pc_afl_grp_nbus_work_pk on pegadata.nbus_work "NNUW" (cost=0.42..2.38 rows=1 width=27) (actual time=0.022..0.022 rows=1 loops=1,493,284)

  • Output: "NNUW".pxcommitdatetime, "NNUW".pxsavedatetime, "NNUW".pxcoverinskey, "NNUW".pxcoveredcount, "NNUW".pxcoveredcountopen, "NNUW".pxcoveredcountunsatisfied, "NNUW".pxcreatedatetime, "NNUW".pxcreateopname, "NNUW".pxcreateoperator, "NNUW".pxcreatesystemid, "NNUW".pxflowcount, "NNUW".pxinsname, "NNUW".pxobjclass, "NNUW".pxupdatedatetime, "NNUW".pxupdateopname, "NNUW".pxupdateoperator, "NNUW".pxupdatesystemid, "NNUW".pxurgencywork, "NNUW".pyacktimestamp, "NNUW".pyagefromdate, "NNUW".pychargeamount, "NNUW".pychargeto, "NNUW".pycontactchannel, "NNUW".pycontacttype, "NNUW".pycuslevel, "NNUW".pycustomer, "NNUW".pycustomerenterprise, "NNUW".pycustomername, "NNUW".pycustomerorg, "NNUW".pycustomersatisfiedtimestamp, "NNUW".pydescription, "NNUW".pyeffortactual, "NNUW".pyeffortestimate, "NNUW".pyeffortestimatetimestamp, "NNUW".pyelapsedcustomerack, "NNUW".pyelapsedcustomerunsatisfied, "NNUW".pyelapsedpastdeadline, "NNUW".pyelapsedpastgoal, "NNUW".pyelapsedstatusnew, "NNUW".pyelapsedstatusopen, "NNUW".pyelapsedstatuspending, "NNUW".pyeventid, "NNUW".pyfoldertype, "NNUW".pyid,
  • Index Cond: (("NNUW".pzinskey)::text = ("WBCC".pxrefobjectkey)::text)
  • Filter: ((("NNUW".pxobjclass)::text ~~ 'AFL-Grp-NBUS-Work%'::text) AND (("NNUW".companycode_1)::text = '01'::text))
  • Rows Removed by Filter: 0
  • Worker 0: actual time=0.023..0.023 rows=1 loops=666,053
Planning time : 0.711 ms
Execution time : 22,366.777 ms