explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wUhcf : Optimization ch123

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.225 24,407.871 ↑ 12.0 1 1

GroupAggregate (cost=3,983.66..3,984.02 rows=12 width=81) (actual time=24,407.871..24,407.871 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))
  • "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
2. 4.097 24,407.646 ↓ 101.8 1,222 1

Sort (cost=3,983.66..3,983.69 rows=12 width=81) (actual time=24,407.590..24,407.646 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
3. 4.904 24,403.549 ↓ 101.8 1,222 1

Nested Loop Left Join (cost=1,020.16..3,983.44 rows=12 width=81) (actual time=16.418..24,403.549 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))
4. 2.018 7.525 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=0.70..7.21 rows=1 width=98) (actual time=0.047..7.525 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))
5. 0.013 0.013 ↑ 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.012..0.013 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))
6. 5.494 5.494 ↓ 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.033..5.494 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,
  • 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)
7. 4.888 24,391.120 ↑ 94.0 1 1,222

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

  • Output: "WBCC".pxassignedoperatorid, count("WBCC".pylabel)
  • Group Key: "WBCC".pxassignedoperatorid
8. 3,876.184 24,386.232 ↑ 47.0 2 1,222

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

  • Output: "WBCC".pxassignedoperatorid, (PARTIAL count("WBCC".pylabel))
  • Workers Planned: 1
  • Workers Launched: 1
9. 112.424 20,510.048 ↑ 94.0 1 2,444 / 2

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

  • Output: "WBCC".pxassignedoperatorid, PARTIAL count("WBCC".pylabel)
  • Group Key: "WBCC".pxassignedoperatorid
  • Worker 0: actual time=14.946..14.946 rows=1 loops=1,222
10. 849.290 20,397.624 ↑ 1.1 606 2,444 / 2

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

  • Output: "WBCC".pxassignedoperatorid, "WBCC".pylabel
  • Inner Unique: true
  • Worker 0: actual time=0.075..14.862 rows=534 loops=1,222
11. 1,487.785 1,628.926 ↑ 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.217..1.333 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=785,338
  • Worker 0: actual time=0.031..1.083 rows=538 loops=1,222
12. 141.141 141.141 ↑ 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.231..0.231 rows=1,222 loops=1,222)

  • Index Cond: (("WBCC".pxassignedoperatorid)::text = 'DestinationCode'::text)
13. 17,919.408 17,919.408 ↑ 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.024..0.024 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, "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
  • 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.025..0.025 rows=1 loops=657,459