explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pVVt

Settings
# exclusive inclusive rows x rows loops node
1. 0.106 1.631 ↓ 5.0 20 1

Sort (cost=1,500.59..1,500.60 rows=4 width=749) (actual time=1.629..1.631 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: quicksort Memory: 44kB
2. 0.062 1.525 ↓ 5.0 20 1

Hash Semi Join (cost=1,070.44..1,500.55 rows=4 width=749) (actual time=1.143..1.525 rows=20 loops=1)

  • Hash Cond: (bankruptcy_cases.id = bankruptcy_cases_1.id)
3. 0.015 0.569 ↓ 2.9 20 1

Nested Loop Left Join (cost=60.17..490.18 rows=7 width=717) (actual time=0.229..0.569 rows=20 loops=1)

4. 0.015 0.534 ↓ 2.9 20 1

Nested Loop Left Join (cost=60.03..489.03 rows=7 width=725) (actual time=0.217..0.534 rows=20 loops=1)

5. 0.021 0.479 ↓ 5.0 20 1

Nested Loop Anti Join (cost=59.61..442.62 rows=4 width=717) (actual time=0.208..0.479 rows=20 loops=1)

6. 0.012 0.418 ↓ 5.0 20 1

Nested Loop Left Join (cost=59.18..400.82 rows=4 width=717) (actual time=0.189..0.418 rows=20 loops=1)

7. 0.008 0.346 ↓ 5.0 20 1

Nested Loop Left Join (cost=58.75..398.65 rows=4 width=471) (actual time=0.166..0.346 rows=20 loops=1)

8. 0.024 0.278 ↓ 5.0 20 1

Nested Loop Left Join (cost=58.33..359.38 rows=4 width=409) (actual time=0.148..0.278 rows=20 loops=1)

  • Filter: ((ownerships.ended_at IS NULL) AND (ownerships.ended_at IS NULL))
9. 0.010 0.214 ↓ 2.9 20 1

Nested Loop (cost=57.90..278.17 rows=7 width=353) (actual time=0.133..0.214 rows=20 loops=1)

10. 0.078 0.164 ↓ 2.0 20 1

Index Scan using claims_pkey on claims (cost=57.62..207.16 rows=10 width=199) (actual time=0.123..0.164 rows=20 loops=1)

  • Index Cond: (id = ANY ('{1097407,1097405,1097400,1097406,1097404,1097402,1097401,1097399,1097398,1097397,1097396,1097395,1097394,1097393,1097392,1097391,1097390,1097388,1097387,1097386}'::bigint[]))
  • Filter: ((deleted_at IS NULL) AND show_in_marketplace AND (NOT (hashed SubPlan 1)))
11.          

SubPlan (for Index Scan)

12. 0.000 0.086 ↑ 1.5 2 1

Nested Loop (cost=4.26..57.19 rows=3 width=8) (actual time=0.064..0.086 rows=2 loops=1)

13. 0.001 0.076 ↑ 1.5 2 1

Nested Loop (cost=3.83..55.56 rows=3 width=8) (actual time=0.056..0.076 rows=2 loops=1)

14. 0.014 0.057 ↑ 1.0 6 1

Hash Semi Join (cost=3.40..4.89 rows=6 width=8) (actual time=0.049..0.057 rows=6 loops=1)

  • Hash Cond: (negotiations_1.id = negotiation_events.negotiation_id)
15. 0.010 0.010 ↑ 1.0 30 1

Seq Scan on negotiations negotiations_1 (cost=0.00..1.30 rows=30 width=16) (actual time=0.007..0.010 rows=30 loops=1)

16. 0.001 0.033 ↑ 1.0 6 1

Hash (cost=3.33..3.33 rows=6 width=16) (actual time=0.033..0.033 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.010 0.032 ↑ 1.0 6 1

Hash Join (cost=1.68..3.33 rows=6 width=16) (actual time=0.027..0.032 rows=6 loops=1)

  • Hash Cond: (negotiation_events.negotiation_id = negotiations_2.id)
18. 0.009 0.009 ↑ 1.0 6 1

Seq Scan on negotiation_events (cost=0.00..1.64 rows=6 width=8) (actual time=0.005..0.009 rows=6 loops=1)

  • Filter: (action = 2)
  • Rows Removed by Filter: 45
19. 0.007 0.013 ↑ 1.0 30 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
20. 0.006 0.006 ↑ 1.0 30 1

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

21. 0.018 0.018 ↓ 0.0 0 6

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

  • Index Cond: (id = negotiations_1.ownership_id)
  • Filter: ((deleted_at IS NULL) AND (ended_at IS NULL))
  • Rows Removed by Filter: 1
22. 0.010 0.010 ↑ 1.0 1 2

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

  • Index Cond: (id = ownerships_1.claim_id)
  • Filter: (deleted_at IS NULL)
23. 0.040 0.040 ↑ 1.0 1 20

Index Scan using bankruptcy_cases_pkey on bankruptcy_cases (cost=0.28..7.10 rows=1 width=154) (actual time=0.002..0.002 rows=1 loops=20)

  • Index Cond: (id = claims.bankruptcy_case_id)
  • 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))
24. 0.040 0.040 ↑ 2.0 1 20

Index Scan using index_ownerships_on_claim_id on ownerships (cost=0.43..11.58 rows=2 width=56) (actual time=0.002..0.002 rows=1 loops=20)

  • Index Cond: (claim_id = claims.id)
  • Filter: (deleted_at IS NULL)
25. 0.060 0.060 ↑ 2.0 1 20

Index Scan using index_portions_on_claim_id on portions (cost=0.42..9.80 rows=2 width=62) (actual time=0.002..0.003 rows=1 loops=20)

  • Index Cond: (claim_id = claims.id)
  • Filter: (deleted_at IS NULL)
26. 0.060 0.060 ↑ 1.0 1 20

Index Scan using legal_entities_pkey on legal_entities (cost=0.43..0.54 rows=1 width=246) (actual time=0.003..0.003 rows=1 loops=20)

  • Index Cond: (id = ownerships.legal_entity_id)
  • Filter: (deleted_at IS NULL)
27. 0.040 0.040 ↓ 0.0 0 20

Index Only Scan using index_portions_on_claim_id_and_claim_class_and_status on portions portions_1 (cost=0.42..8.45 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=20)

  • Index Cond: ((claim_id = claims.id) AND (claim_class = 2))
  • Heap Fetches: 0
28. 0.040 0.040 ↑ 2.0 1 20

Index Scan using index_ownerships_on_claim_id on ownerships ownerships_claims (cost=0.43..11.58 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=20)

  • Index Cond: (claim_id = claims.id)
  • Filter: (deleted_at IS NULL)
29. 0.020 0.020 ↓ 0.0 0 20

Index Only Scan using index_negotiations_on_ownership_id on negotiations (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=20)

  • Index Cond: (ownership_id = ownerships_claims.id)
  • Heap Fetches: 0
30. 0.003 0.894 ↑ 138.3 11 1

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

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

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

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

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

33.          

CTE bankruptcy_cases__recursive

34. 0.006 0.048 ↑ 138.3 11 1

Recursive Union (cost=0.28..835.22 rows=1,521 width=48) (actual time=0.010..0.048 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.008 0.032 ↑ 30.4 5 2

Nested Loop (cost=0.28..79.65 rows=152 width=48) (actual time=0.012..0.016 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.001..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.345 0.827 ↑ 1.0 2,462 1

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

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

  • Filter: (deleted_at IS NULL)
Planning time : 4.188 ms
Execution time : 1.878 ms