explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Oag

Settings
# exclusive inclusive rows x rows loops node
1. 0.990 32.990 ↑ 1.0 2,000 1

Limit (cost=116,810.28..56,624,008,574.75 rows=2,000 width=10) (actual time=0.214..32.990 rows=2,000 loops=1)

  • Output: p.process_id
  • Buffers: shared hit=77
  • -> Index Scan using cwprocess_completed_process_parent_status on eoc.cwprocess_completed t (cost=0.14..116809.72 rows=121081 widt h=760) (actual time=0.006..0.110 rows=61 loops=1)
  • Output: t.process_id, t.parent_id, t.root_id, t.status, t.bucket_no, t.process_metadatype, t.revision, t.metadatatype_ver, t. order_id, t.process_data_id, t.parent_activity, t.pdoc_ind, t.process_data, t.starttime, t.changedstatustime, t.priority, t.order_type, t.order_item_id, t.creation_date, t.last_used_date, t.restart_date, t.pe_conversion_number
  • Buffers: shared hit=62
  • -> Index Only Scan using cwprocess_completed_time_process_full_status on eoc.cwprocess_completed p (cost=0.56..5341331710143.26 row s=188660 width=10) (actual time=0.212..32.742 rows=2000 loops=1)
  • Output: p.process_id
  • Index Cond: ((p.changedstatustime < (current_date - 95)) AND (p.status = ANY ('{3,6}'::numeric[])))
  • Filter: (NOT (SubPlan 3))
  • Heap Fetches: 0
  • Buffers: shared hit=77
2.          

SubPlan (forLimit)

3. 32.000 32.000 ↓ 0.0 0 2,000

Sort (cost=14,155,972.88..14,186,496.89 rows=12,209,605 width=32) (actual time=0.016..0.016 rows=0 loops=2,000)

  • Output: connectby_cte.siblingssortcol
  • Sort Key: connectby_cte.siblingssortcol
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=62
  • -> CTE Scan on prior connectby_cte (cost=0.00..732576.30 rows=12209605 width=32) (actual time=0.015..0.015 rows=0 loo ps=2000)
  • Output: connectby_cte.siblingssortcol
  • Buffers: shared hit=62
4.          

CTE prior

5. 2.000 30.000 ↓ 0.0 0 2,000

Recursive Union (cost=0.00..11,819,303.69 rows=12,209,605 width=100) (actual time=0.015..0.015 rows=0 loops=2,000)

  • Buffers: shared hit=62
6. 0.000 26.000 ↓ 0.0 0 2,000

WindowAgg (cost=0.00..7,587.22 rows=605 width=36) (actual time=0.013..0.013 rows=0 loops=2,000)

  • Output: c.process_id, c.parent_id, ARRAY[c.process_id], ARRAY[row_number() OVER (?)]
  • Buffers: shared hit=62
7. 26.000 26.000 ↓ 0.0 0 2,000

CTE Scan on c (cost=0.00..7,567.56 rows=605 width=36) (actual time=0.013..0.013 rows=0 loops=2,000)

  • Output: c.process_id, c.parent_id
  • Filter: (c.parent_id = p.process_id)
  • Rows Removed by Filter: 61
  • Buffers: shared hit=62
8. 0.000 2.000 ↓ 0.0 0 2,000

WindowAgg (cost=18,230.52..1,107,914.02 rows=1,220,900 width=100) (actual time=0.001..0.001 rows=0 loops=2,000)

  • Output: c_1.process_id, c_1.parent_id, array_append(prior.recursionpath, c_1.process_id), array_append(pr ior.siblingssortcol, row_number() OVER (?))
  • -> Merge Join (cost=18230.52..1062130.27 rows=1220900 width=100) (actual time=0.001..0.001 rows=0 loops =2000)
  • Output: c_1.process_id, c_1.parent_id, prior.recursionpath, prior.siblingssortcol
  • Merge Cond: (prior.process_id = c_1.parent_id)
  • Join Filter: connectby_cyclecheck(prior.recursionpath, c_1.process_id)
9. 2.000 2.000 ↓ 0.0 0 2,000

Sort (cost=743.02..758.15 rows=6,050 width=82) (actual time=0.001..0.001 rows=0 loops=2,000)

  • Output: prior.recursionpath, prior.siblingssortcol, prior.process_id
  • Sort Key: prior.process_id
  • Sort Method: quicksort Memory: 25kB
  • -> WorkTable Scan on prior (cost=0.00..363.00 rows=6050 width=82) (actual time=0.000..0.000 rows=0 loops=2000)
  • Output: prior.recursionpath, prior.siblingssortcol, prior.process_id
10. 0.000 0.000 ↓ 0.0 0

Sort (cost=17,487.49..17,790.20 rows=121,081 width=36) (never executed)

  • Output: c_1.process_id, c_1.parent_id
  • Sort Key: c_1.parent_id
11. 0.000 0.000 ↓ 0.0 0

CTE Scan on c c_1 (cost=0.00..7,264.86 rows=121,081 width=36) (never executed)

  • Output: c_1.process_id, c_1.parent_id