explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rkpM : Optimization for: plan #irEk

Settings

Optimization path:

Optimization(s) for this plan:

# 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".pxgoalexe
  • Group Key: "PC0".pyworkbasket, "PC0".pyworkgroup, "PC0".pylabel, (count("WBCC".pylabel))
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".pxinsnam
  • 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".px
  • Index Cond: ((("AssignWB".pxassignedoperatorid)::text = 'DestinationCode'::text) AND (("AssignWB".pxworkgroup)::text = 'NewBusiness'::text) AND (("AssignWB".pxapp
  • 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=1222
10. 20,256.483 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=1222
  • -> Parallel Bitmap Heap Scan on pegadata.pc_assign_workbasket "WBCC" (cost=19.04..1221.99 rows=730 width=46) (actual time=0.217..1.333 rows=611 loop
  • Output: "WBCC".pxcommitdatetime, "WBCC".pxsavedatetime, "WBCC".pxapplication, "WBCC".pxassignedoperatorid, "WBCC".pxassignedorg, "WBCC".pxassign
  • Recheck Cond: (("WBCC".pxassignedoperatorid)::text = 'DestinationCode'::text)
  • Filter: (("WBCC".pxobjclass)::text = 'Assign-WorkBasket'::text)
  • Heap Blocks: exact=785338
  • Worker 0: actual time=0.031..1.083 rows=538 loops=1222
  • -> 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=14
  • Output: "NNUW".pxcommitdatetime, "NNUW".pxsavedatetime, "NNUW".pxcoverinskey, "NNUW".pxcoveredcount, "NNUW".pxcoveredcountopen, "NNUW".pxcovered
  • 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=657459
11. 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)