explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cbbP

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 1,918.666 ↑ 1.0 20 1

Limit (cost=147,782.88..147,782.93 rows=20 width=231) (actual time=1,918.662..1,918.666 rows=20 loops=1)

2. 0.569 1,918.662 ↑ 10,904.7 20 1

Sort (cost=147,782.88..148,328.11 rows=218,094 width=231) (actual time=1,918.661..1,918.662 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: 35kB
3. 112.463 1,918.093 ↑ 1,178.9 185 1

Hash Semi Join (cost=53,581.55..141,979.48 rows=218,094 width=231) (actual time=1,199.160..1,918.093 rows=185 loops=1)

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

Gather (cost=52,572.38..135,437.45 rows=352,618 width=207) (actual time=1,196.924..1,804.529 rows=582,762 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 67.011 1,610.837 ↓ 1.3 194,254 3 / 3

Hash Left Join (cost=51,572.38..99,175.65 rows=146,924 width=207) (actual time=1,199.944..1,610.837 rows=194,254 loops=3)

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

Parallel Hash Left Join (cost=51,570.71..98,622.99 rows=146,924 width=215) (actual time=1,199.905..1,543.804 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. 108.713 494.724 ↓ 1.3 194,254 3 / 3

Parallel Hash Anti Join (cost=10,829.73..43,049.89 rows=147,427 width=207) (actual time=44.050..494.724 rows=194,254 loops=3)

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

Hash Join (cost=158.92..30,500.59 rows=152,020 width=207) (actual time=1.565..343.700 rows=198,571 loops=3)

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

Parallel Seq Scan on claims (cost=57.19..29,794.17 rows=229,959 width=199) (actual time=0.339..211.755 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.003 0.256 ↑ 1.5 2 3 / 3

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

12. 0.005 0.205 ↑ 1.5 2 3 / 3

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

13. 0.022 0.140 ↑ 1.0 6 3 / 3

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

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

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

15. 0.003 0.080 ↑ 1.0 6 3 / 3

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

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

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

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

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

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

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

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

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

20. 0.060 0.060 ↓ 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.010..0.010 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.048 0.048 ↑ 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.024..0.024 rows=1 loops=6)

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

Hash (cost=81.54..81.54 rows=1,615 width=8) (actual time=1.119..1.119 rows=1,647 loops=3)

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

Seq Scan on bankruptcy_cases (cost=0.00..81.54 rows=1,615 width=8) (actual time=0.065..0.840 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.735 42.311 ↑ 1.3 10,863 3 / 3

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

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

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

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

  • Buckets: 65,536 Batches: 32 Memory Usage: 3,712kB
27. 175.417 175.417 ↑ 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.068..175.417 rows=659,038 loops=3)

  • Filter: (deleted_at IS NULL)
28. 0.007 0.022 ↑ 1.0 30 3 / 3

Hash (cost=1.30..1.30 rows=30 width=8) (actual time=0.022..0.022 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.005 1.101 ↑ 137.4 11 1

Hash (cost=990.27..990.27 rows=1,511 width=16) (actual time=1.100..1.101 rows=11 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 17kB
31. 0.019 1.096 ↑ 137.4 11 1

Hash Join (cost=940.97..990.27 rows=1,511 width=16) (actual time=1.045..1.096 rows=11 loops=1)

  • Hash Cond: (bankruptcy_cases__recursive.id = bankruptcy_cases_1.id)
32. 0.059 0.059 ↑ 137.4 11 1

CTE Scan on bankruptcy_cases__recursive (cost=835.00..865.22 rows=1,511 width=48) (actual time=0.011..0.059 rows=11 loops=1)

33.          

CTE bankruptcy_cases__recursive

34. 0.005 0.053 ↑ 137.4 11 1

Recursive Union (cost=0.28..835.00 rows=1,511 width=48) (actual time=0.010..0.053 rows=11 loops=1)

35. 0.010 0.010 ↑ 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.009..0.010 rows=1 loops=1)

  • Index Cond: (id = 758)
  • Filter: (deleted_at IS NULL)
36. 0.014 0.038 ↑ 30.2 5 2

Nested Loop (cost=0.28..79.65 rows=151 width=48) (actual time=0.016..0.019 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.325 1.018 ↓ 1.0 2,462 1

Hash (cost=75.43..75.43 rows=2,443 width=8) (actual time=1.018..1.018 rows=2,462 loops=1)

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

Seq Scan on bankruptcy_cases bankruptcy_cases_1 (cost=0.00..75.43 rows=2,443 width=8) (actual time=0.009..0.693 rows=2,462 loops=1)

  • Filter: (deleted_at IS NULL)
Planning time : 3.302 ms
Execution time : 1,960.953 ms