explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GVsY

Settings
# exclusive inclusive rows x rows loops node
1. 0.487 19.161 ↑ 1.0 1 1

Aggregate (cost=666,088.50..666,088.51 rows=1 width=0) (actual time=19.161..19.161 rows=1 loops=1)

2.          

CTE delete_set

3. 0.711 10.604 ↑ 1.0 1,000 1

Limit (cost=0.00..2,862.41 rows=1,000 width=4) (actual time=9.182..10.604 rows=1,000 loops=1)

4. 9.893 9.893 ↑ 1.8 1,000 1

Index Scan using cases_case_id_pk on cases (cost=0.00..5,146.62 rows=1,798 width=4) (actual time=9.178..9.893 rows=1,000 loops=1)

  • Index Cond: (case_id <= 63,919)
  • Filter: (account_id = 160)
  • Rows Removed by Filter: 23,960
5.          

CTE delete_acks

6. 0.001 3.228 ↓ 0.0 0 1

Delete on acks (cost=175.36..212.16 rows=2,120 width=34) (actual time=3.228..3.228 rows=0 loops=1)

7. 0.419 3.227 ↓ 0.0 0 1

Merge Join (cost=175.36..212.16 rows=2,120 width=34) (actual time=3.227..3.227 rows=0 loops=1)

  • Merge Cond: (d.case_id = acks.case_id)
8. 0.798 1.315 ↑ 1.0 1,000 1

Sort (cost=69.83..72.33 rows=1,000 width=32) (actual time=0.995..1.315 rows=1,000 loops=1)

  • Sort Key: d.case_id
  • Sort Method: quicksort Memory: 103kB
9. 0.517 0.517 ↑ 1.0 1,000 1

CTE Scan on delete_set d (cost=0.00..20.00 rows=1,000 width=32) (actual time=0.002..0.517 rows=1,000 loops=1)

10. 0.870 1.493 ↑ 7.1 195 1

Sort (cost=105.53..109.01 rows=1,391 width=10) (actual time=1.432..1.493 rows=195 loops=1)

  • Sort Key: acks.case_id
  • Sort Method: quicksort Memory: 114kB
11. 0.623 0.623 ↑ 1.0 1,391 1

Seq Scan on acks (cost=0.00..32.91 rows=1,391 width=10) (actual time=0.005..0.623 rows=1,391 loops=1)

12.          

CTE delete_case_notification_errors

13. 0.002 0.919 ↓ 0.0 0 1

Delete on case_notification_errors (cost=2.01..29.26 rows=225 width=34) (actual time=0.919..0.919 rows=0 loops=1)

14. 0.410 0.917 ↓ 0.0 0 1

Hash Join (cost=2.01..29.26 rows=225 width=34) (actual time=0.917..0.917 rows=0 loops=1)

  • Hash Cond: (d.case_id = case_notification_errors.case_id)
15. 0.484 0.484 ↑ 1.0 1,000 1

CTE Scan on delete_set d (cost=0.00..20.00 rows=1,000 width=32) (actual time=0.007..0.484 rows=1,000 loops=1)

16. 0.008 0.023 ↑ 2.8 16 1

Hash (cost=1.45..1.45 rows=45 width=10) (actual time=0.023..0.023 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
17. 0.015 0.015 ↑ 2.8 16 1

Seq Scan on case_notification_errors (cost=0.00..1.45 rows=45 width=10) (actual time=0.008..0.015 rows=16 loops=1)

18.          

CTE delete_case_notifications

19. 0.003 315.641 ↓ 0.0 0 1

Delete on case_notifications (cost=30,402.04..31,762.59 rows=8,516 width=34) (actual time=315.641..315.641 rows=0 loops=1)

20. 4.294 315.638 ↓ 0.0 0 1

Merge Join (cost=30,402.04..31,762.59 rows=8,516 width=34) (actual time=315.638..315.638 rows=0 loops=1)

  • Merge Cond: (case_notifications.case_id = d.case_id)
21. 201.440 309.952 ↑ 23.3 10,604 1

Sort (cost=30,332.21..30,948.61 rows=246,562 width=10) (actual time=304.899..309.952 rows=10,604 loops=1)

  • Sort Key: case_notifications.case_id
  • Sort Method: external merge Disk: 5,272kB
22. 108.512 108.512 ↑ 1.0 245,697 1

Seq Scan on case_notifications (cost=0.00..4,036.62 rows=246,562 width=10) (actual time=0.006..108.512 rows=245,697 loops=1)

23. 0.818 1.392 ↑ 1.0 1,000 1

Sort (cost=69.83..72.33 rows=1,000 width=32) (actual time=1.053..1.392 rows=1,000 loops=1)

  • Sort Key: d.case_id
  • Sort Method: quicksort Memory: 103kB
24. 0.574 0.574 ↑ 1.0 1,000 1

CTE Scan on delete_set d (cost=0.00..20.00 rows=1,000 width=32) (actual time=0.008..0.574 rows=1,000 loops=1)

25.          

CTE delete_case_relation

26. 0.001 1,316.871 ↓ 0.0 0 1

Delete on case_relation (cost=613.00..617,143.34 rows=4,763 width=34) (actual time=1,316.871..1,316.871 rows=0 loops=1)

27. 1.957 1,316.870 ↓ 0.0 0 1

Nested Loop (cost=613.00..617,143.34 rows=4,763 width=34) (actual time=1,316.870..1,316.870 rows=0 loops=1)

28. 0.913 0.913 ↑ 1.0 1,000 1

CTE Scan on delete_set d (cost=0.00..20.00 rows=1,000 width=32) (actual time=0.008..0.913 rows=1,000 loops=1)

29. 1.000 1,314.000 ↓ 0.0 0 1,000

Bitmap Heap Scan on case_relation (cost=613.00..617.07 rows=5 width=14) (actual time=1.314..1.314 rows=0 loops=1,000)

  • Recheck Cond: ((case_id = d.case_id) OR (related_case_id = d.case_id))
30. 2.000 1,313.000 ↓ 0.0 0 1,000

BitmapOr (cost=613.00..613.00 rows=5 width=0) (actual time=1.313..1.313 rows=0 loops=1,000)

31. 2.000 2.000 ↓ 0.0 0 1,000

Bitmap Index Scan on case_relation_pkey (cost=0.00..1.38 rows=3 width=0) (actual time=0.002..0.002 rows=0 loops=1,000)

  • Index Cond: (case_id = d.case_id)
32. 1,309.000 1,309.000 ↓ 0.0 0 1,000

Bitmap Index Scan on case_relation_pkey (cost=0.00..611.62 rows=2 width=0) (actual time=1.309..1.309 rows=0 loops=1,000)

  • Index Cond: (related_case_id = d.case_id)
33.          

CTE delete_case_tags

34. 0.001 2.653 ↓ 0.0 0 1

Delete on case_tags (cost=0.00..6,005.00 rows=2,032 width=34) (actual time=2.653..2.653 rows=0 loops=1)

35. 1.131 2.652 ↓ 0.0 0 1

Nested Loop (cost=0.00..6,005.00 rows=2,032 width=34) (actual time=2.652..2.652 rows=0 loops=1)

36. 0.521 0.521 ↑ 1.0 1,000 1

CTE Scan on delete_set d (cost=0.00..20.00 rows=1,000 width=32) (actual time=0.008..0.521 rows=1,000 loops=1)

37. 1.000 1.000 ↓ 0.0 0 1,000

Index Scan using case_tags_case_id_idx on case_tags (cost=0.00..5.97 rows=2 width=10) (actual time=0.001..0.001 rows=0 loops=1,000)

  • Index Cond: (case_id = d.case_id)
38.          

CTE delete_cases_forced_clear

39. 0.001 1.673 ↓ 0.0 0 1

Delete on cases_forced_clear (cost=69.83..163.87 rows=2,639 width=34) (actual time=1.673..1.673 rows=0 loops=1)

40. 0.347 1.672 ↓ 0.0 0 1

Merge Join (cost=69.83..163.87 rows=2,639 width=34) (actual time=1.672..1.672 rows=0 loops=1)

  • Merge Cond: (cases_forced_clear.case_id = d.case_id)
41. 0.010 0.010 ↑ 847.0 1 1

Index Scan using cases_forced_clear_case_id_idx on cases_forced_clear (cost=0.00..52.34 rows=847 width=10) (actual time=0.010..0.010 rows=1 loops=1)

42. 0.802 1.315 ↑ 1.0 1,000 1

Sort (cost=69.83..72.33 rows=1,000 width=32) (actual time=0.967..1.315 rows=1,000 loops=1)

  • Sort Key: d.case_id
  • Sort Method: quicksort Memory: 103kB
43. 0.513 0.513 ↑ 1.0 1,000 1

CTE Scan on delete_set d (cost=0.00..20.00 rows=1,000 width=32) (actual time=0.004..0.513 rows=1,000 loops=1)

44.          

CTE delete_cases

45. 2.475 17.862 ↑ 1.0 1,000 1

Delete on cases (cost=0.00..7,887.36 rows=1,000 width=34) (actual time=9.234..17.862 rows=1,000 loops=1)

46. 1.893 15.387 ↑ 1.0 1,000 1

Nested Loop (cost=0.00..7,887.36 rows=1,000 width=34) (actual time=9.220..15.387 rows=1,000 loops=1)

47. 11.494 11.494 ↑ 1.0 1,000 1

CTE Scan on delete_set d (cost=0.00..20.00 rows=1,000 width=32) (actual time=9.209..11.494 rows=1,000 loops=1)

48. 2.000 2.000 ↑ 1.0 1 1,000

Index Scan using cases_case_id_pk on cases (cost=0.00..7.86 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=1,000)

  • Index Cond: (case_id = d.case_id)
49. 18.674 18.674 ↑ 1.0 1,000 1

CTE Scan on delete_cases (cost=0.00..20.00 rows=1,000 width=0) (actual time=9.238..18.674 rows=1,000 loops=1)

Total runtime : 36,379.455 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint acks_case_id_fkey on cases 216.538 ms 1000 0.217 ms
for constraint case_notification_errors_case_id_fkey on cases 33.016 ms 1000 0.033 ms
for constraint case_notifications_case_id_fkey on cases 25,786.467 ms 1000 25.786 ms
for constraint case_relation_case_id_fkey on cases 53.200 ms 1000 0.053 ms
for constraint case_relation_related_case_id_fkey on cases 8,562.929 ms 1000 8.563 ms
for constraint cases_forced_clear_case_id_fkey on cases 43.468 ms 1000 0.043 ms
for constraint case_tags_case_id_fkey on cases 18.698 ms 1000 0.019 ms