explain.depesz.com

PostgreSQL's explain analyze made readable

Result: btbu

Settings
# exclusive inclusive rows x rows loops node
1. 0.942 4,849.489 ↑ 2,063.9 189 1

Sort (cost=669,198.06..670,173.24 rows=390,073 width=749) (actual time=4,849.469..4,849.489 rows=189 loops=1)

  • Sort Key: (ROW(("substring"((claims.claim_number)::text, 'w*[0-9]+'::text))::integer, "substring"((claims.claim_number)::text, '^[\\D^\\s]+'::text))) DESC
  • Sort Method: quicksort Memory: 215kB
2. 94.749 4,848.547 ↑ 2,063.9 189 1

Hash Semi Join (cost=112,600.20..374,316.18 rows=390,073 width=749) (actual time=3,832.488..4,848.547 rows=189 loops=1)

  • Hash Cond: (bankruptcy_cases.id = bankruptcy_cases_1.id)
3. 491.881 4,751.054 ↓ 1.8 1,110,583 1

Hash Left Join (cost=111,589.93..363,408.20 rows=631,401 width=717) (actual time=3,817.174..4,751.054 rows=1,110,583 loops=1)

  • Hash Cond: (ownerships_claims.id = negotiations.ownership_id)
4. 141.397 4,259.147 ↓ 1.8 1,110,582 1

Gather (cost=111,588.26..361,038.67 rows=631,401 width=725) (actual time=3,817.139..4,259.147 rows=1,110,582 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 859.569 4,117.750 ↓ 1.4 370,194 3 / 3

Parallel Hash Left Join (cost=110,588.26..296,898.57 rows=263,084 width=725) (actual time=3,805.329..4,117.750 rows=370,194 loops=3)

  • Hash Cond: (claims.id = ownerships_claims.claim_id)
6. 99.300 2,975.393 ↓ 1.5 222,210 3 / 3

Nested Loop Left Join (cost=70,650.82..224,611.16 rows=146,878 width=717) (actual time=1,632.917..2,975.393 rows=222,210 loops=3)

7. 690.942 1,987.254 ↓ 1.5 222,210 3 / 3

Parallel Hash Left Join (cost=70,650.40..145,154.53 rows=146,878 width=471) (actual time=1,632.849..1,987.254 rows=222,210 loops=3)

  • Hash Cond: (claims.id = ownerships.claim_id)
  • Filter: (ownerships.ended_at IS NULL)
  • Rows Removed by Filter: 147,984
8. 93.664 980.545 ↓ 1.5 222,210 3 / 3

Parallel Hash Anti Join (cost=26,690.96..75,665.53 rows=147,377 width=415) (actual time=490.760..980.545 rows=222,210 loops=3)

  • Hash Cond: (claims.id = portions_1.claim_id)
9. 134.066 855.835 ↓ 1.5 228,781 3 / 3

Hash Join (cost=16,020.15..63,116.87 rows=151,968 width=415) (actual time=459.551..855.835 rows=228,781 loops=3)

  • Hash Cond: (claims.bankruptcy_case_id = bankruptcy_cases.id)
10. 475.156 720.991 ↓ 1.8 418,094 3 / 3

Parallel Hash Left Join (cost=15,918.04..62,410.07 rows=229,960 width=261) (actual time=458.744..720.991 rows=418,094 loops=3)

  • Hash Cond: (claims.id = portions.claim_id)
11. 125.393 132.727 ↓ 1.6 367,934 3 / 3

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

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

SubPlan (for Parallel Seq Scan)

13. 0.011 7.334 ↑ 1.5 2 3 / 3

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

14. 0.005 7.125 ↑ 1.5 2 3 / 3

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

15. 0.017 1.024 ↑ 1.0 6 3 / 3

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

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

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

17. 0.003 0.587 ↑ 1.0 6 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.013 0.584 ↑ 1.0 6 3 / 3

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

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

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

  • Filter: (action = 2)
  • Rows Removed by Filter: 45
20. 0.008 0.023 ↑ 1.0 30 3 / 3

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

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

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

22. 6.096 6.096 ↓ 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=1.016..1.016 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
23. 0.198 0.198 ↑ 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.099..0.099 rows=1 loops=6)

  • Index Cond: (id = ownerships_1.claim_id)
  • Filter: (deleted_at IS NULL)
24. 76.216 113.108 ↑ 1.2 206,857 3 / 3

Parallel Hash (cost=9,850.71..9,850.71 rows=258,571 width=62) (actual time=113.108..113.108 rows=206,857 loops=3)

  • Buckets: 65,536 Batches: 32 Memory Usage: 2,464kB
25. 36.892 36.892 ↑ 1.2 206,857 3 / 3

Parallel Seq Scan on portions (cost=0.00..9,850.71 rows=258,571 width=62) (actual time=0.012..36.892 rows=206,857 loops=3)

  • Filter: (deleted_at IS NULL)
26. 0.188 0.778 ↓ 1.0 1,647 3 / 3

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 284kB
27. 0.590 0.590 ↓ 1.0 1,647 3 / 3

Seq Scan on bankruptcy_cases (cost=0.00..81.78 rows=1,627 width=154) (actual time=0.044..0.590 rows=1,647 loops=3)

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

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

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

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

  • Filter: (claim_class = 2)
  • Rows Removed by Filter: 195,993
30. 206.451 315.767 ↑ 1.2 659,038 3 / 3

Parallel Hash (cost=25,616.97..25,616.97 rows=823,797 width=56) (actual time=315.767..315.767 rows=659,038 loops=3)

  • Buckets: 65,536 Batches: 64 Memory Usage: 2,848kB
31. 109.316 109.316 ↑ 1.2 659,038 3 / 3

Parallel Seq Scan on ownerships (cost=0.00..25,616.97 rows=823,797 width=56) (actual time=0.016..109.316 rows=659,038 loops=3)

  • Filter: (deleted_at IS NULL)
32. 888.839 888.839 ↑ 1.0 1 666,629 / 3

Index Scan using legal_entities_pkey on legal_entities (cost=0.43..0.54 rows=1 width=246) (actual time=0.004..0.004 rows=1 loops=666,629)

  • Index Cond: (id = ownerships.legal_entity_id)
  • Filter: (deleted_at IS NULL)
33. 163.031 282.788 ↑ 1.2 659,038 3 / 3

Parallel Hash (cost=25,616.97..25,616.97 rows=823,797 width=16) (actual time=282.788..282.788 rows=659,038 loops=3)

  • Buckets: 131,072 Batches: 32 Memory Usage: 4,000kB
34. 119.757 119.757 ↑ 1.2 659,038 3 / 3

Parallel Seq Scan on ownerships ownerships_claims (cost=0.00..25,616.97 rows=823,797 width=16) (actual time=0.042..119.757 rows=659,038 loops=3)

  • Filter: (deleted_at IS NULL)
35. 0.007 0.026 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=8) (actual time=0.025..0.026 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
36. 0.019 0.019 ↑ 1.0 30 1

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

37. 0.004 2.744 ↑ 138.3 11 1

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

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

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

  • Hash Cond: (bankruptcy_cases__recursive.id = bankruptcy_cases_1.id)
39. 1.198 1.198 ↑ 138.3 11 1

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

40.          

CTE bankruptcy_cases__recursive

41. 0.008 1.190 ↑ 138.3 11 1

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

42. 0.314 0.314 ↑ 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.313..0.314 rows=1 loops=1)

  • Index Cond: (id = 758)
  • Filter: (deleted_at IS NULL)
43. 0.019 0.868 ↑ 30.4 5 2

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

44. 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)

45. 0.847 0.847 ↑ 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.077..0.077 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)))
46. 0.339 1.533 ↑ 1.0 2,462 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 129kB
47. 1.194 1.194 ↑ 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.013..1.194 rows=2,462 loops=1)

  • Filter: (deleted_at IS NULL)
Planning time : 12.201 ms
Execution time : 4,944.201 ms