explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K59e

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.069 9,224.921 ↑ 16.7 12 1

Left Join (cost=2,169,977.97..2,169,999.76 rows=200 width=128) (actual time=9,224.866..9,224.921 rows=12 loops=1)

  • Output: a.channel, a.draft, nvl((d.submitted)::numeric, '0'::numeric), a.completed, nvl((b.mt)::numeric, '0'::numeric), nvl(c.duplicate, '0'::numeric), nvl(((a.inprogress)::numeric - nvl((b.mt)::numeric, '0'::numeric)), '0'::numeric), a.abortedserver, a.abortedclient, nvl((e.stuck)::numeric, '0'::numeric)
  • Hash Cond: ((a.channel)::text = (d.channel)::text)
  • Buffers: shared hit=939,577 read=486 dirtied=902, temp read=84,868 written=21,216
2.          

CTE ctecworderinstance

3. 6,226.820 6,226.820 ↑ 3.8 597,862 1

Seq Scan on eocpuser.cworderinstance (cost=0.00..1,379,169.75 rows=2,289,741 width=2,204) (actual time=0.031..6,226.820 rows=597,862 loops=1)

  • Output: cworderinstance.cwdocid, cworderinstance.metadatatype, cworderinstance.status, cworderinstance.state, cworderinstance.visualkey, cworderinstance.productcode, cworderinstance.creationdate, cworderinstance.createdby, cworderinstance.updatedby, cworderinstance.lastupdateddate, cworderinstance.parentorder, cworderinstance.owner, cworderinstance.state2, cworderinstance.hasattachment, cworderinstance.metadatatype_ver, cworderinstance.original_order_id, cworderinstance.source_order_id, cworderinstance.kind_of_order, cworderinstance.order_phase, cworderinstance.project_id, cworderinstance.process_id, cworderinstance.cworderstamp, cworderinstance.cwdocstamp, cworderinstance.app_name, cworderinstance.duedate, cworderinstance.cwuserrole, cworderinstance.basketid, cworderinstance.ostate, cworderinstance.customerid, cworderinstance.accountid, cworderinstance.ordertype, cworderinstance.ordersubtype, cworderinstance.relatedorder, cworderinstance.ordernum, cworderinstance.ordver, cworderinstance.effectivedate, cworderinstance.submittedby, cworderinstance.submitteddate, cworderinstance.price, cworderinstance.onetimeprice, cworderinstance.pricedon, cworderinstance.correlationid, cworderinstance.quoteid, cworderinstance.channel, cworderinstance.expirationdate, cworderinstance.quoteexpirationdate, cworderinstance.assignedpriority, cworderinstance.requestedstartdate, cworderinstance.requestedcompletiondate,nce.mode_sc, cworderinstance.islocked, cworderinstance.relatedentities, cworderinstance.requester, cworderinstance.bispecification, cworderinstance.relatedscs, cworderinstance.quoteon, cworderinstance.completiondate, cworderinstance.relatedorders, cworderinstance.extendedstate, cworderinstance.orderrole, cworderinstance.orderidref, cworderinstance.prevostate
  • Filter: (((cworderinstance.createdby)::text <> 'JMS-jms:destination:258'::text) AND (cworderinstance.creationdate >= '27-MAY-20 00:00:00'::timestamp without time zone) AND (cworderinstance.creationdate <= '06-SEP-20 23:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 7,265,693
  • Buffers: shared hit=932,805 dirtied=539
4. 0.030 8,987.777 ↑ 16.7 12 1

Merge Left Join (cost=630,592.32..630,601.39 rows=200 width=120) (actual time=8,987.737..8,987.777 rows=12 loops=1)

  • Output: a.channel, a.draft, a.completed, a.inprogress, a.abortedserver, a.abortedclient, b.mt, c.duplicate, e.stuck
  • Merge Cond: ((a.channel)::text = (e.channel)::text)
  • Buffers: shared hit=939,577 read=486 dirtied=902, temp read=63,651 written=21,216
5. 0.016 8,762.711 ↑ 16.7 12 1

Merge Left Join (cost=466,988.91..466,997.44 rows=200 width=112) (actual time=8,762.680..8,762.711 rows=12 loops=1)

  • Output: a.channel, a.draft, a.completed, a.inprogress, a.abortedserver, a.abortedclient, b.mt, c.duplicate
  • Merge Cond: ((a.channel)::text = (c.channel)::text)
  • Buffers: shared hit=934,891 read=482 dirtied=902, temp read=42,434 written=21,216
6. 0.028 8,477.488 ↑ 16.7 12 1

Merge Left Join (cost=323,724.19..323,725.26 rows=200 width=80) (actual time=8,477.466..8,477.488 rows=12 loops=1)

  • Output: a.channel, a.draft, a.completed, a.inprogress, a.abortedserver, a.abortedclient, b.mt
  • Merge Cond: ((a.channel)::text = (b.channel)::text)
  • Buffers: shared hit=934,891 read=482 dirtied=902, temp read=21,217 written=21,216
7. 0.023 8,042.810 ↑ 16.7 12 1

Sort (cost=177,484.22..177,484.72 rows=200 width=72) (actual time=8,042.807..8,042.810 rows=12 loops=1)

  • Output: a.channel, a.draft, a.completed, a.inprogress, a.abortedserver, a.abortedclient
  • Sort Key: a.channel
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=932,805 dirtied=539, temp written=21,215
8. 0.007 8,042.787 ↑ 16.7 12 1

Subquery Scan on a (cost=177,470.07..177,476.57 rows=200 width=72) (actual time=8,042.780..8,042.787 rows=12 loops=1)

  • Output: a.channel, a.draft, a.completed, a.inprogress, a.abortedserver, a.abortedclient
  • Buffers: shared hit=932,805 dirtied=539, temp written=21,215
9. 0.046 8,042.780 ↑ 16.7 12 1

Sort (cost=177,470.07..177,470.57 rows=200 width=324) (actual time=8,042.777..8,042.780 rows=12 loops=1)

  • Output: (DECODE( ((upper((oi.channel)::text)))::character varying , NULL::character varying , 'Other'::character varying , ((upper((oi.channel)::text)))::character varying )), (sum(DECODE( oi.ostate , 'O_NR_DRA'::character varying , 1 , 0 ))), (sum(DECODE( oi.ostate , 'C_C'::character varying , 1 , 0 ))), (sum(DECODE( oi.ostate , 'C_A'::character varying , 1 , 0 ))), (sum(DECODE( oi.ostate , 'C_X'::character varying , 1 , 0 ))), (sum(DECODE( oi.ostate , 'O_R_PRO'::character varying , 1 , 0 ))), (NULL::bigint), (upper((oi.channel)::text))
  • Sort Key: (DECODE( ((upper((oi.channel)::text)))::character varying , NULL::character varying , 'Other'::character varying , ((upper((oi.channel)::text)))::character varying )) DESC
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=932,805 dirtied=539, temp written=21,215
10. 505.456 8,042.734 ↑ 16.7 12 1

HashAggregate (cost=177,454.93..177,462.43 rows=200 width=324) (actual time=8,042.726..8,042.734 rows=12 loops=1)

  • Output: DECODE( ((upper((oi.channel)::text)))::character varying , NULL::character varying , 'Other'::character varying , ((upper((oi.channel)::text)))::character varying ), sum(DECODE( oi.os 1 , 0 )), sum(DECODE( oi.ostate , 'C_X'::character varying , 1 , 0 )), sum(DECODE( oi.ostate , 'O_R_PRO'::character varying , 1 , 0 )), NULL::bigint, (upper((oi.channel)::text))
  • Group Key: upper((oi.channel)::text)
  • Buffers: shared hit=932,805 dirtied=539, temp written=21,215
11. 7,537.278 7,537.278 ↑ 3.8 597,862 1

CTE Scan on ctecworderinstance oi (cost=0.00..143,108.81 rows=2,289,741 width=324) (actual time=0.055..7,537.278 rows=597,862 loops=1)

  • Output: upper((oi.channel)::text), oi.channel, oi.ostate
  • Buffers: shared hit=932,805 dirtied=539, temp written=21,215
12. 0.054 434.650 ↓ 4.5 9 1

Sort (cost=146,239.97..146,239.98 rows=2 width=40) (actual time=434.648..434.650 rows=9 loops=1)

  • Output: b.mt, b.channel
  • Sort Key: b.channel
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2,086 read=482 dirtied=363, temp read=21,217 written=1
13. 0.003 434.596 ↓ 4.5 9 1

Subquery Scan on b (cost=146,239.83..146,239.96 rows=2 width=40) (actual time=434.590..434.596 rows=9 loops=1)

  • Output: b.mt, b.channel
  • Buffers: shared hit=2,086 read=482 dirtied=363, temp read=21,217 written=1
14. 0.687 434.593 ↓ 4.5 9 1

HashAggregate (cost=146,239.83..146,239.90 rows=2 width=274) (actual time=434.590..434.593 rows=9 loops=1)

  • Output: nvl((upper((ctecworderinstance.channel)::text))::character varying, 'Other'::character varying), count(1), ctecworderinstance.channel
  • Group Key: ctecworderinstance.channel
  • Buffers: shared hit=2,086 read=482 dirtied=363, temp read=21,217 written=1
15. 226.109 433.906 ↑ 3.8 1,496 1

Hash Semi Join (cost=2,872.72..146,211.21 rows=5,724 width=274) (actual time=207.917..433.906 rows=1,496 loops=1)

  • Output: ctecworderinstance.channel
  • Hash Cond: ((ctecworderinstance.cwdocid)::text = (cwpworklist.order_id)::text)
  • Buffers: shared hit=2,086 read=482 dirtied=363, temp read=21,217 written=1
  • -> CTE Scan on ctecworderinstance (cost=0.00..143108.81 rows=11,449 width=324) (actual time=0.029..224.836 rows=1,524 loops=1)tance.state, ctecworderinstance.visualkey, ctecworderinstance.productcode, ctecworderinstance.creationdate, ctecworderinstance.createdby, ctecworderinstance.updatedby, ctecworderinstance.lastupdateddate, ctecworderinstance.parentorder, ctecworderinstance.owner, ctecworderinstance.state2, ctecworderinstance.hasattachment, ctecworderinstance.metadatatype_ver, ctecworderinstance.original_order_id, ctecworderinstance.source_order_id, ctecworderinstance.kind_of_order, ctecworderinstance.order_phase, ctecworderinstance.project_id, ctecworderinstance.process_id, ctecworderinstance.cworderstamp, ctecworderinstance.cwdocstamp, ctecworderinstance.app_name, ctecworderinstance.duedate, ctecworderinstance.cwuserrole, ctecworderinstance.basketid, ctecworderinstance.ostate, ctecworderinstance.customerid, ctecworderinstance.accountid, ctecworderinstance.ordertype, ctecworderinstance.ordersubtype, ctecworderinstance.relatedorder, ctecworderinstance.ordernum, ctecworderinstance.ordver, ctecworderinstance.effectivedate, ctecworderinstance.submittedby, ctecworderinstance.submitteddate, ctecworderinstance.price, ctecworderinstance.onetimeprice, ctecworderinstance.pricedon, ctecworderinstance.correlationid, ctecworderinstance.quoteid, ctecworderinstance.channel, ctecworderinstance.expirationdate, ctecworderinstance.quoteexpirationdate, ctecworderinstance.assignedpriority, ctecworderinstance.requestedstartdate, ctecworderinstance.requestedcompletiondate, ctecworderinstance.description, ctecworderinstance.bitype, ctecworderinstance.externalorderid, ctecworderinstance.isbundled, ctecworderinstance.notes, ctecworderinstance.attrs, ctecworderinstance.mode_sc, ctecwe.quoteon, ctecworderinstance.completiondate, ctecworderinstance.relatedorders, ctecworderinstance.extendedstate, ctecworderinstance.orderrole, ctecworderinstance.orderidref, ctecworderinstance.prevostate
  • Filter: ((ctecworderinstance.ostate)::text = 'O_R_PRO'::text)
  • Rows Removed by Filter: 596,338
  • Buffers: temp read=21,217 written=1
16. 2.377 207.797 ↑ 1.4 9,019 1

Hash (cost=2,465.24..2,465.24 rows=12,538 width=15) (actual time=207.797..207.797 rows=9,019 loops=1)

  • Output: cwpworklist.order_id
  • Buckets: 16,384 Batches: 1 Memory Usage: 549kB
  • Buffers: shared hit=2,086 read=482 dirtied=363
17. 205.420 205.420 ↑ 1.4 9,019 1

Index Only Scan using cwpworklistord on eocpuser.cwpworklist (cost=0.41..2,465.24 rows=12,538 width=15) (actual time=2.162..205.420 rows=9,019 loops=1)

  • Output: cwpworklist.order_id
  • Heap Fetches: 21,076 read=482 dirtied=363 Buffers: shared hit=208--More--
18. 0.020 285.207 ↑ 66.3 3 1

Sort (cost=143,264.72..143,265.22 rows=199 width=64) (actual time=285.206..285.207 rows=3 loops=1)

  • Output: c.duplicate, c.channel
  • Sort Key: c.channel
  • Sort Method: quicksort Memory: 25kB
  • Buffers: temp read=21,217
19. 0.001 285.187 ↑ 66.3 3 1

Subquery Scan on c (cost=143,236.23..143,257.12 rows=199 width=64) (actual time=285.170..285.187 rows=3 loops=1)

  • Output: c.duplicate, c.channel
  • Buffers: temp read=21,217
20. 0.052 285.186 ↑ 66.3 3 1

GroupAggregate (cost=143,236.23..143,251.15 rows=199 width=282) (actual time=285.169..285.186 rows=3 loops=1)

  • Output: nvl((upper((ctecworderinstance_1.channel)::text))::character varying, 'Other'::character varying), sum((count(1))), ctecworderinstance_1.channel
  • Group Key: ctecworderinstance_1.channel
  • Buffers: temp read=21,217
21. 0.136 285.134 ↑ 4.4 45 1

Sort (cost=143,236.23..143,236.73 rows=199 width=356) (actual time=285.130..285.134 rows=45 loops=1)

  • Output: ctecworderinstance_1.channel, ctecworderinstance_1.customerid, (count(1))tecworderinstance_1.customerid Sort Key: ctecworderinstance_1.channel, c--More--
  • Sort Method: quicksort Memory: 28kB
  • Buffers: temp read=21,217
22. 1.724 284.998 ↑ 4.4 45 1

HashAggregate (cost=143,222.16..143,228.63 rows=199 width=356) (actual time=284.746..284.998 rows=45 loops=1)

  • Output: ctecworderinstance_1.channel, ctecworderinstance_1.customerid, count(1)
  • Group Key: ctecworderinstance_1.channel, ctecworderinstance_1.customerid
  • Filter: (count(1) > 1)
  • Rows Removed by Filter: 1,399
  • Buffers: temp read=21,217
23. 283.274 283.274 ↑ 7.4 1,524 1

CTE Scan on ctecworderinstance ctecworderinstance_1 (cost=0.00..143,108.81 rows=11,335 width=356) (actual time=0.015..283.274 rows=1,524 loops=1)

  • Output: ctecworderinstance_1.cwdocid, ctecworderinstance_1.metadatatype, ctecworderinstance_1.status, ctecworderinstance_1.state, ctecworderinstance_1.visualkey, ctecworderinstance_1.productcode, ctecworderinstance_1.creationdate, ctecworderinstance_1.createdby, ctecworderinstance_1.updatedby, ctecworderinstance_1.lastupdateddate, ctecworderinstance_1.parentorder, ctecworderinstance_1.owner, ctecworderinstance_1.state2, cstance_1.kind_of_order, ctecworderinstance_1.order_phase, ctecworderinstance_1.project_id, ctecworderinstance_1.process_id, ctecworderinstance_1.cworderstamp, ctecworderinstance_1.cwdocstamp, ctecworderinstance_1.app_name, ctecworderinstance_1.duedate, ctecworderinstance_1.cwuserrole, ctecworderinstance_1.basketid, ctecworderinstance_1.ostate, ctecworderinstance_1.customerid, ctecworderinstance_1.accountid, ctecworderinstance_1.ordertype, ctecworderinstance_1.ordersubtype, ctecworderinstance_1.relatedorder, ctecworderinstance_1.ordernum, ctecworderinstance_1.ordver, ctecworderinstance_1.effectivedate, ctecworderinstance_1.submittedby, ctecworderinstance_1.submitteddate, ctecworderinstance_1.price, ctecworderinstance_1.onetimeprice, ctecworderinstance_1.pricedon, ctecworderinstance_1.correlationid, ctecworderinstance_1.quoteid, ctecworderinstance_1.channel, ctecworderinstance_1.expirationdate, ctecworderinstance_1.quoteexpirationdate, ctecworderinstance_1.assignedpriority, ctecworderinstance_1.requestedstartdate, ctecworderinstance_1.requestedcompletiondate, ctecworderinstance_1.description, ctecworderinstance_1.bitype, ctecworderinstance_1.externalorderid, ctecworderinstance_1.isbundled, ctecworderinstance_1.notes, ctecworderinstance_1.attrs, ctecworderinstance_1.mode_sc, ctecworderinstance_1.islocked, ctecworderinstance_1.relatedentities, ctecworderinstance_1.requester, ctecworderinstance_1.bispecification, ctecworderinstance_1.relatedscs, ctecworderinstance_1.quoteon, ctecworderinstance_1.completiondate, ctecworderinstance_1.relatedorders, ctecworderinstance_1.extendedstate, ctecworderinstance_1.orderrole, ctecworderinstance_1.orderidref, ctecworderinstance_1.prevostate1.customerid IS NOT NULL) AND (ctecworderinstance_1.channel IS NOT NULL) AND ((ctecworderinstance_1.ostate)::text = 'O_R_PRO'::text))
  • Rows Removed by Filter: 596,338
  • Buffers: temp read=21,217
24. 0.037 225.036 ↓ 4.0 4 1

Sort (cost=163,603.41..163,603.41 rows=1 width=40) (actual time=225.036..225.036 rows=4 loops=1)

  • Output: e.stuck, e.channel
  • Sort Key: e.channel
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4,686 read=4, temp read=21,217
25. 0.000 224.999 ↓ 4.0 4 1

Subquery Scan on e (cost=163,603.33..163,603.40 rows=1 width=40) (actual time=224.997..224.999 rows=4 loops=1)

  • Output: e.stuck, e.channel
  • Buffers: shared hit=4,686 read=4, temp read=21,217
26. 0.073 224.999 ↓ 4.0 4 1

HashAggregate (cost=163,603.33..163,603.37 rows=1 width=274) (actual time=224.997..224.999 rows=4 loops=1)

  • Output: nvl(((upper((x.channel)::text)))::character varying, 'Other'::character varying), count(1), (upper((x.channel)::text))
  • Group Key: upper((x.channel)::text)
  • Buffers: shared hit=4,686 read=4, temp read=21,217
27. 215.782 224.926 ↑ 68.1 28 1

Nested Loop Anti Join (cost=0.41..163,593.79 rows=1,908 width=274) (actual time=9.742..224.926 rows=28 loops=1)

  • Output: upper((x.channel)::text), x.channel
  • Buffers: shared hit=4,686 read=4, temp read=21,217
  • -> CTE Scan on ctecworderinstance x (cost=0.00..160281.87 rows=3,816 width=324) (actual time=0.065..214.678 rows=1,524 loops=1) Output: x.cwdocid, x.metadatatype, x.status, x.state, x.visualkey, x.productcode, x.creationdate, x.createdby, x.updatedby, x.lastupdateddate, x.parentorder, x.owner, x.state2, x.hasattachment, x.metadatatype_ver, x.original_order_id, x.source_order_id, x.kind_of_order, x.order_phase, x.project_id, x.process_id, x.cworderstamp, x.cwdocstamp, x.app_name, x.duedate, x.cwuserrole, x.basketid, x.ostate, x.customerid, x.accountid, x.ordertype, x.ordersubtype, x.relatedorder, x.ordernum, x.ordver, x.effectivedate, x.submittedby, x.submitteddate, x.price, x.onetimeprice, x.pricedon, x.correlationid, x.quoteid, x.channel, x.expirationdate, x.quoteexpirationdate, x.assignedpriority, x.requestedstartdate, x.requestedcompletiondate, x.description, x.bitype, x.externalorderid, x.isbundled, x.notes, x.attrs, x.mode_sc, x.islocked, x.relatedentities, x.requester, x.bispecification, x.relatedscs, x.quoteon, x.completiondate, x.relatedorders, x.extendedstate, x.orderrole, x.orderidref, x.prevostate
  • Filter: (((x.ostate)::text = 'O_R_PRO'::text) AND (x.submitteddate <= (sysdate - 0.02083333333333333333)))
  • Rows Removed by Filter: 596,338
  • Output: y.order_id
  • Index Cond: (y.order_id = (x.cwdocid)::text)
  • Heap Fetches: 114
  • Buffers: shared hit=4,686 read=4
28. 9.144 9.144 ↑ 1.0 1 1,524

Buffers: temp read=21217puser.cwpworklist y (cost=0.41..0.98 rows=1 width=15) (actual time=0.006..0.006 rows=1 loops=1,524)

29. 0.011 237.075 ↑ 22.1 9 1

Hash (cost=160,209.44..160,209.44 rows=199 width=40) (actual time=237.075..237.075 rows=9 loops=1)

  • Output: d.submitted, d.channel
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: temp read=21,217
30. 0.002 237.064 ↑ 22.1 9 1

Subquery Scan on d (cost=160,196.00..160,209.44 rows=199 width=40) (actual time=237.059..237.064 rows=9 loops=1)

  • Output: d.submitted, d.channel
  • Buffers: temp read=21,217
31. 1.295 237.062 ↑ 22.1 9 1

HashAggregate (cost=160,196.00..160,203.47 rows=199 width=274) (actual time=237.058..237.062 rows=9 loops=1)

  • Output: nvl(((upper((ctecworderinstance_2.channel)::text)))::character varying, 'Other'::character varying), count(1), (upper((ctecworderinstance_2.channel)::text))
  • Group Key: upper((ctecworderinstance_2.channel)::text)
  • Buffers: temp read=21,217
32. 235.767 235.767 ↑ 644.3 3,536 1

CTE Scan on ctecworderinstance ctecworderinstance_2 (cost=0.00..148,804.54 rows=2,278,292 width=274) (actual time=0.024..235.767 rows=3,536 loops=1)

  • Output: upper((ctecworderinstance_2.channel)::text), ctecworderinstance_2.channel
  • Filter: ((ctecworderinstance_2.ostate)::text <> 'O_NR_DRA'::text)
  • Rows Removed by Filter: 594,326
  • Buffers: temp read=21,217
Planning time : 1.050 ms
Execution time : 9,273.356 ms