explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 44w

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 72,339.627 ↑ 1.0 10 1

Limit (cost=0.43..930,416,515.47 rows=10 width=8) (actual time=7,405.219..72,339.627 rows=10 loops=1)

2. 222.576 72,339.616 ↑ 141,599.9 10 1

Index Scan using cwprocess_completed_stat_date on cwprocess_completed p (cost=0.43..131,746,885,486,677.02 rows=1,415,999 width=8) (actual time=7,405.219..72,339.616 rows=10 loops=1)

  • Index Cond: (status = ANY ('{3,6}'::integer[]))
  • Filter: ((((current_date)::timestamp without time zone - changedstatustime) > '60 days'::interval) AND (NOT (SubPlan 2)))
  • Rows Removed by Filter: 650366
3.          

SubPlan (forIndex Scan)

4. 0.040 72,117.040 ↓ 0.0 0 10

Sort (cost=15,506,943.70..15,511,277.42 rows=1,733,489 width=32) (actual time=7,211.704..7,211.704 rows=0 loops=10)

  • Sort Key: connectby_cte.siblingssortcol
  • Sort Method: quicksort Memory: 25kB
5.          

CTE prior

6. 0.060 72,116.980 ↓ 0.0 0 10

Recursive Union (cost=0.00..15,246,432.78 rows=1,750,999 width=82) (actual time=7,211.698..7,211.698 rows=0 loops=10)

7. 0.050 6,177.750 ↓ 0.0 0 10

WindowAgg (cost=0.00..171,034.28 rows=229 width=18) (actual time=617.775..617.775 rows=0 loops=10)

8. 6,177.700 6,177.700 ↓ 0.0 0 10

Seq Scan on cwprocess_completed c (cost=0.00..171,031.41 rows=229 width=18) (actual time=617.770..617.770 rows=0 loops=10)

  • Filter: (parent_id = p.process_id)
  • Rows Removed by Filter: 8614894
9. 351.820 65,939.170 ↓ 0.0 0 10

WindowAgg (cost=1,318,713.49..1,504,037.85 rows=175,077 width=82) (actual time=6,593.917..6,593.917 rows=0 loops=10)

10. 0.080 65,587.350 ↓ 0.0 0 10

Merge Join (cost=1,318,713.49..1,500,974.00 rows=175,077 width=82) (actual time=6,558.735..6,558.735 rows=0 loops=10)

  • Merge Cond: (c_1.parent_id = prior.process_id)
  • Join Filter: connectby_cyclecheck(prior.recursionpath, c_1.process_id)
11. 54,597.100 65,587.150 ↑ 8,614,913.0 1 10

Sort (cost=1,318,539.89..1,340,077.18 rows=8,614,913 width=18) (actual time=6,558.715..6,558.715 rows=1 loops=10)

  • Sort Key: c_1.parent_id
12. 10,990.050 10,990.050 ↑ 1.0 8,614,894 10

Seq Scan on cwprocess_completed c_1 (cost=0.00..149,494.13 rows=8,614,913 width=18) (actual time=0.011..1,099.005 rows=8,614,894 loops=10)

13. 0.110 0.120 ↓ 0.0 0 10

Sort (cost=173.59..179.32 rows=2,290 width=72) (actual time=0.012..0.012 rows=0 loops=10)

  • Sort Key: prior.process_id
  • Sort Method: quicksort Memory: 25kB
14. 0.010 0.010 ↓ 0.0 0 10

WorkTable Scan on prior (cost=0.00..45.80 rows=2,290 width=72) (actual time=0.001..0.001 rows=0 loops=10)

15. 72,117.000 72,117.000 ↓ 0.0 0 10

CTE Scan on prior connectby_cte (cost=0.00..39,397.48 rows=1,733,489 width=32) (actual time=7,211.700..7,211.700 rows=0 loops=10)

  • Filter: (status <> ALL ('{3,6}'::integer[]))