explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gC9i

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,137.513 ↑ 1.0 20 1

Limit (cost=147,751.15..147,751.20 rows=20 width=231) (actual time=1,137.509..1,137.513 rows=20 loops=1)

2. 0.393 1,137.510 ↑ 10,888.5 20 1

Sort (cost=147,751.15..148,295.57 rows=217,769 width=231) (actual time=1,137.508..1,137.510 rows=20 loops=1)

  • Sort Key: claims.date_filed DESC, (ROW(("substring"((claims.claim_number)::text, 'w*[0-9]+'::text))::integer, "substring"((claims.claim_number)::text, '^[\\D^\\s]+'::text))) DESC
  • Sort Method: top-N heapsort Memory: 34kB
3. 68.621 1,137.117 ↑ 1,177.1 185 1

Hash Semi Join (cost=53,583.04..141,956.39 rows=217,769 width=231) (actual time=726.741..1,137.117 rows=185 loops=1)

  • Hash Cond: (bankruptcy_cases.id = bankruptcy_cases_1.id)
4. 114.976 1,067.616 ↓ 1.7 582,762 1

Gather (cost=52,572.77..135,420.44 rows=352,498 width=207) (actual time=725.505..1,067.616 rows=582,762 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 39.982 952.640 ↓ 1.3 194,254 3 / 3

Hash Left Join (cost=51,572.77..99,170.64 rows=146,874 width=207) (actual time=719.007..952.640 rows=194,254 loops=3)

  • Hash Cond: (ownerships.id = negotiations.ownership_id)
6. 357.396 912.637 ↓ 1.3 194,254 3 / 3

Parallel Hash Left Join (cost=51,571.10..98,618.16 rows=146,874 width=215) (actual time=718.974..912.637 rows=194,254 loops=3)

  • Hash Cond: (claims.id = ownerships.claim_id)
  • Filter: (ownerships.ended_at IS NULL)
  • Rows Removed by Filter: 147,218
7. 52.215 269.403 ↓ 1.3 194,254 3 / 3

Parallel Hash Anti Join (cost=10,830.12..43,049.64 rows=147,377 width=207) (actual time=27.501..269.403 rows=194,254 loops=3)

  • Hash Cond: (claims.id = portions.claim_id)
8. 70.487 190.809 ↓ 1.3 198,571 3 / 3

Hash Join (cost=159.31..30,500.98 rows=151,968 width=207) (actual time=0.959..190.809 rows=198,571 loops=3)

  • Hash Cond: (claims.bankruptcy_case_id = bankruptcy_cases.id)
9. 119.490 119.617 ↓ 1.6 367,934 3 / 3

Parallel Seq Scan on claims (cost=57.19..29,794.19 rows=229,960 width=199) (actual time=0.183..119.617 rows=367,934 loops=3)

  • Filter: ((deleted_at IS NULL) AND show_in_marketplace AND (NOT (hashed SubPlan 1)))
  • Rows Removed by Filter: 2
10.          

SubPlan (for Parallel Seq Scan)

11. 0.001 0.127 ↑ 1.5 2 3 / 3

Nested Loop (cost=4.26..57.19 rows=3 width=8) (actual time=0.106..0.127 rows=2 loops=3)

12. 0.003 0.110 ↑ 1.5 2 3 / 3

Nested Loop (cost=3.83..55.56 rows=3 width=8) (actual time=0.090..0.110 rows=2 loops=3)

13. 0.013 0.077 ↑ 1.0 6 3 / 3

Hash Semi Join (cost=3.40..4.89 rows=6 width=8) (actual time=0.071..0.077 rows=6 loops=3)

  • Hash Cond: (negotiations_1.id = negotiation_events.negotiation_id)
14. 0.021 0.021 ↑ 1.0 30 3 / 3

Seq Scan on negotiations negotiations_1 (cost=0.00..1.30 rows=30 width=16) (actual time=0.019..0.021 rows=30 loops=3)

15. 0.002 0.043 ↑ 1.0 6 3 / 3

Hash (cost=3.33..3.33 rows=6 width=16) (actual time=0.042..0.043 rows=6 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.009 0.041 ↑ 1.0 6 3 / 3

Hash Join (cost=1.68..3.33 rows=6 width=16) (actual time=0.036..0.041 rows=6 loops=3)

  • Hash Cond: (negotiation_events.negotiation_id = negotiations_2.id)
17. 0.022 0.022 ↑ 1.0 6 3 / 3

Seq Scan on negotiation_events (cost=0.00..1.64 rows=6 width=8) (actual time=0.018..0.022 rows=6 loops=3)

  • Filter: (action = 2)
  • Rows Removed by Filter: 45
18. 0.005 0.010 ↑ 1.0 30 3 / 3

Hash (cost=1.30..1.30 rows=30 width=8) (actual time=0.010..0.010 rows=30 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
19. 0.005 0.005 ↑ 1.0 30 3 / 3

Seq Scan on negotiations negotiations_2 (cost=0.00..1.30 rows=30 width=8) (actual time=0.002..0.005 rows=30 loops=3)

20. 0.030 0.030 ↓ 0.0 0 18 / 3

Index Scan using ownerships_pkey on ownerships ownerships_1 (cost=0.43..8.45 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=18)

  • Index Cond: (id = negotiations_1.ownership_id)
  • Filter: ((deleted_at IS NULL) AND (ended_at IS NULL))
  • Rows Removed by Filter: 1
21. 0.016 0.016 ↑ 1.0 1 6 / 3

Index Scan using claims_pkey on claims claims_1 (cost=0.43..0.54 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=6)

  • Index Cond: (id = ownerships_1.claim_id)
  • Filter: (deleted_at IS NULL)
22. 0.186 0.705 ↓ 1.0 1,647 3 / 3

Hash (cost=81.78..81.78 rows=1,627 width=8) (actual time=0.705..0.705 rows=1,647 loops=3)

  • Buckets: 2,048 Batches: 1 Memory Usage: 81kB
23. 0.519 0.519 ↓ 1.0 1,647 3 / 3

Seq Scan on bankruptcy_cases (cost=0.00..81.78 rows=1,627 width=8) (actual time=0.031..0.519 rows=1,647 loops=3)

  • Filter: ((deleted_at IS NULL) AND (deleted_at IS NULL) AND show_in_marketplace AND (date_filed >= '2018-09-14 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 815
24. 2.687 26.379 ↑ 1.3 10,863 3 / 3

Parallel Hash (cost=10,497.14..10,497.14 rows=13,894 width=8) (actual time=26.379..26.379 rows=10,863 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,824kB
25. 23.692 23.692 ↑ 1.3 10,863 3 / 3

Parallel Seq Scan on portions (cost=0.00..10,497.14 rows=13,894 width=8) (actual time=0.057..23.692 rows=10,863 loops=3)

  • Filter: (claim_class = 2)
  • Rows Removed by Filter: 195,993
26. 168.721 285.838 ↑ 1.2 659,038 3 / 3

Parallel Hash (cost=25,616.77..25,616.77 rows=823,777 width=24) (actual time=285.838..285.838 rows=659,038 loops=3)

  • Buckets: 65,536 Batches: 32 Memory Usage: 3,712kB
27. 117.117 117.117 ↑ 1.2 659,038 3 / 3

Parallel Seq Scan on ownerships (cost=0.00..25,616.77 rows=823,777 width=24) (actual time=0.049..117.117 rows=659,038 loops=3)

  • Filter: (deleted_at IS NULL)
28. 0.006 0.021 ↑ 1.0 30 3 / 3

Hash (cost=1.30..1.30 rows=30 width=8) (actual time=0.021..0.021 rows=30 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
29. 0.015 0.015 ↑ 1.0 30 3 / 3

Seq Scan on negotiations (cost=0.00..1.30 rows=30 width=8) (actual time=0.011..0.015 rows=30 loops=3)

30. 0.002 0.880 ↑ 138.3 11 1

Hash (cost=991.25..991.25 rows=1,521 width=16) (actual time=0.880..0.880 rows=11 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 17kB
31. 0.009 0.878 ↑ 138.3 11 1

Hash Join (cost=941.62..991.25 rows=1,521 width=16) (actual time=0.826..0.878 rows=11 loops=1)

  • Hash Cond: (bankruptcy_cases__recursive.id = bankruptcy_cases_1.id)
32. 0.058 0.058 ↑ 138.3 11 1

CTE Scan on bankruptcy_cases__recursive (cost=835.22..865.64 rows=1,521 width=48) (actual time=0.010..0.058 rows=11 loops=1)

33.          

CTE bankruptcy_cases__recursive

34. 0.006 0.050 ↑ 138.3 11 1

Recursive Union (cost=0.28..835.22 rows=1,521 width=48) (actual time=0.008..0.050 rows=11 loops=1)

35. 0.008 0.008 ↑ 1.0 1 1

Index Scan using bankruptcy_cases_pkey on bankruptcy_cases bankruptcy_cases_2 (cost=0.28..8.30 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (id = 758)
  • Filter: (deleted_at IS NULL)
36. 0.012 0.036 ↑ 30.4 5 2

Nested Loop (cost=0.28..79.65 rows=152 width=48) (actual time=0.014..0.018 rows=5 loops=2)

37. 0.002 0.002 ↑ 1.7 6 2

WorkTable Scan on bankruptcy_cases__recursive bankruptcy_cases__recursive_1 (cost=0.00..0.20 rows=10 width=40) (actual time=0.000..0.001 rows=6 loops=2)

38. 0.022 0.022 ↑ 15.0 1 11

Index Scan using index_bankruptcy_cases_on_parent_id on bankruptcy_cases bankruptcy_cases_3 (cost=0.28..7.76 rows=15 width=16) (actual time=0.002..0.002 rows=1 loops=11)

  • Index Cond: (parent_id = bankruptcy_cases__recursive_1.id)
  • Filter: ((deleted_at IS NULL) AND (id <> ALL (bankruptcy_cases__recursive_1.__path)))
39. 0.346 0.811 ↑ 1.0 2,462 1

Hash (cost=75.62..75.62 rows=2,462 width=8) (actual time=0.811..0.811 rows=2,462 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 129kB
40. 0.465 0.465 ↑ 1.0 2,462 1

Seq Scan on bankruptcy_cases bankruptcy_cases_1 (cost=0.00..75.62 rows=2,462 width=8) (actual time=0.008..0.465 rows=2,462 loops=1)

  • Filter: (deleted_at IS NULL)
Planning time : 3.006 ms
Execution time : 1,163.653 ms