explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KAbc : Full query

Settings
# exclusive inclusive rows x rows loops node
1. 5.290 7,798.549 ↑ 1,148.3 189 1

Sort (cost=441,292.73..441,835.33 rows=217,037 width=749) (actual time=7,798.524..7,798.549 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. 125.622 7,793.259 ↑ 1,148.3 189 1

Hash Semi Join (cost=72,663.19..278,135.03 rows=217,037 width=749) (actual time=2,088.966..7,793.259 rows=189 loops=1)

  • Hash Cond: (bankruptcy_cases.id = bankruptcy_cases_1.id)
3. 603.095 7,666.682 ↓ 3.2 1,110,583 1

Hash Left Join (cost=71,652.93..271,617.66 rows=351,312 width=717) (actual time=2,016.617..7,666.682 rows=1,110,583 loops=1)

  • Hash Cond: (ownerships_claims.id = negotiations.ownership_id)
4. 46.278 7,063.571 ↓ 3.2 1,110,582 1

Gather (cost=71,651.25..270,298.52 rows=351,312 width=725) (actual time=2,016.591..7,063.571 rows=1,110,582 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 316.844 7,017.293 ↓ 2.5 370,194 3 / 3

Nested Loop Left Join (cost=70,651.25..234,167.32 rows=146,380 width=725) (actual time=2,010.610..7,017.293 rows=370,194 loops=3)

6. 225.787 5,367.191 ↓ 2.7 222,210 3 / 3

Nested Loop Left Join (cost=70,650.82..181,228.65 rows=81,722 width=717) (actual time=2,010.170..5,367.191 rows=222,210 loops=3)

7. 575.492 2,252.678 ↓ 2.7 222,210 3 / 3

Parallel Hash Left Join (cost=70,650.40..137,019.48 rows=81,722 width=471) (actual time=2,009.115..2,252.678 rows=222,210 loops=3)

  • Hash Cond: (claims.id = portions.claim_id)
8. 90.229 1,524.640 ↓ 2.4 194,254 3 / 3

Parallel Hash Anti Join (cost=54,789.55..109,102.74 rows=81,722 width=409) (actual time=895.122..1,524.640 rows=194,254 loops=3)

  • Hash Cond: (claims.id = portions_1.claim_id)
9. 125.197 1,401.083 ↓ 2.4 198,571 3 / 3

Hash Join (cost=44,118.74..97,390.64 rows=84,268 width=409) (actual time=861.604..1,401.083 rows=198,571 loops=3)

  • Hash Cond: (claims.bankruptcy_case_id = bankruptcy_cases.id)
10. 712.508 1,275.064 ↓ 2.9 367,934 3 / 3

Parallel Hash Left Join (cost=44,016.63..96,953.22 rows=127,516 width=255) (actual time=860.740..1,275.064 rows=367,934 loops=3)

  • Hash Cond: (claims.id = ownerships.claim_id)
  • Filter: ((ownerships.ended_at IS NULL) AND (ownerships.ended_at IS NULL))
  • Rows Removed by Filter: 291,102
11. 157.926 158.119 ↓ 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.258..158.119 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.002 0.193 ↑ 1.5 2 3 / 3

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

14. 0.004 0.171 ↑ 1.5 2 3 / 3

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

15. 0.017 0.131 ↑ 1.0 6 3 / 3

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

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

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

17. 0.002 0.068 ↑ 1.0 6 3 / 3

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

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

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

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

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

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

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

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

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

22. 0.036 0.036 ↓ 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.006..0.006 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.020 0.020 ↑ 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.010..0.010 rows=1 loops=6)

  • Index Cond: (id = ownerships_1.claim_id)
  • Filter: (deleted_at IS NULL)
24. 266.292 404.437 ↑ 1.2 659,038 3 / 3

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

  • Buckets: 65,536 Batches: 64 Memory Usage: 2,848kB
25. 138.145 138.145 ↑ 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.082..138.145 rows=659,038 loops=3)

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

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

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

Seq Scan on bankruptcy_cases (cost=0.00..81.78 rows=1,627 width=154) (actual time=0.050..0.611 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.484 33.328 ↑ 1.3 10,863 3 / 3

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,856kB
29. 29.844 29.844 ↑ 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.057..29.844 rows=10,863 loops=3)

  • Filter: (claim_class = 2)
  • Rows Removed by Filter: 195,993
30. 101.629 152.546 ↑ 1.2 206,857 3 / 3

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

  • Buckets: 65,536 Batches: 32 Memory Usage: 2,464kB
31. 50.917 50.917 ↑ 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.045..50.917 rows=206,857 loops=3)

  • Filter: (deleted_at IS NULL)
32. 2,888.726 2,888.726 ↑ 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.013..0.013 rows=1 loops=666,629)

  • Index Cond: (id = ownerships.legal_entity_id)
  • Filter: (deleted_at IS NULL)
33. 1,333.258 1,333.258 ↑ 1.0 2 666,629 / 3

Index Scan using index_ownerships_on_claim_id on ownerships ownerships_claims (cost=0.43..0.63 rows=2 width=16) (actual time=0.005..0.006 rows=2 loops=666,629)

  • Index Cond: (claim_id = claims.id)
  • Filter: (deleted_at IS NULL)
34. 0.006 0.016 ↑ 1.0 30 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
35. 0.010 0.010 ↑ 1.0 30 1

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

36. 0.005 0.955 ↑ 138.3 11 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 17kB
37. 0.031 0.950 ↑ 138.3 11 1

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

  • Hash Cond: (bankruptcy_cases__recursive.id = bankruptcy_cases_1.id)
38. 0.076 0.076 ↑ 138.3 11 1

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

39.          

CTE bankruptcy_cases__recursive

40. 0.006 0.066 ↑ 138.3 11 1

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

41. 0.012 0.012 ↑ 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.010..0.012 rows=1 loops=1)

  • Index Cond: (id = 758)
  • Filter: (deleted_at IS NULL)
42. 0.013 0.048 ↑ 30.4 5 2

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

43. 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.001..0.001 rows=6 loops=2)

44. 0.033 0.033 ↑ 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.003 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)))
45. 0.373 0.843 ↑ 1.0 2,462 1

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

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

  • Filter: (deleted_at IS NULL)
Planning time : 5.833 ms
Execution time : 7,868.931 ms