explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CaBI : Claim.shown_in_marketplace (with indexes)

Settings
# exclusive inclusive rows x rows loops node
1. 134.280 1,005.146 ↓ 1.7 582,762 1

Gather (cost=52,573.22..135,133.74 rows=352,506 width=199) (actual time=668.545..1,005.146 rows=582,762 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 37.090 870.866 ↓ 1.3 194,254 3 / 3

Hash Left Join (cost=51,573.22..98,883.14 rows=146,878 width=199) (actual time=661.502..870.866 rows=194,254 loops=3)

  • Hash Cond: (ownerships.id = negotiations.ownership_id)
3. 304.549 833.754 ↓ 1.3 194,254 3 / 3

Parallel Hash Left Join (cost=51,571.55..98,330.65 rows=146,878 width=207) (actual time=661.469..833.754 rows=194,254 loops=3)

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

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

  • Hash Cond: (claims.id = portions.claim_id)
5. 67.365 181.482 ↓ 1.3 198,571 3 / 3

Hash Join (cost=159.31..30,500.98 rows=151,968 width=199) (actual time=1.035..181.482 rows=198,571 loops=3)

  • Hash Cond: (claims.bankruptcy_case_id = bankruptcy_cases.id)
6. 113.207 113.354 ↓ 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.195..113.354 rows=367,934 loops=3)

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

SubPlan (for Parallel Seq Scan)

8. 0.002 0.147 ↑ 1.5 2 3 / 3

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

9. 0.004 0.129 ↑ 1.5 2 3 / 3

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

10. 0.013 0.095 ↑ 1.0 6 3 / 3

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

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

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

12. 0.002 0.051 ↑ 1.0 6 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.015 0.049 ↑ 1.0 6 3 / 3

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

  • Hash Cond: (negotiation_events.negotiation_id = negotiations_2.id)
14. 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
15. 0.006 0.012 ↑ 1.0 30 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
16. 0.006 0.006 ↑ 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.006 rows=30 loops=3)

17. 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
18. 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)
19. 0.203 0.763 ↓ 1.0 1,647 3 / 3

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

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

Seq Scan on bankruptcy_cases (cost=0.00..81.78 rows=1,627 width=8) (actual time=0.033..0.560 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
21. 2.488 25.420 ↑ 1.3 10,863 3 / 3

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

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

  • Filter: (claim_class = 2)
  • Rows Removed by Filter: 195,993
23. 159.957 274.782 ↑ 1.2 659,038 3 / 3

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

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

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

  • Filter: (deleted_at IS NULL)
25. 0.008 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
26. 0.014 0.014 ↑ 1.0 30 3 / 3

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

Planning time : 1.647 ms
Execution time : 1,039.577 ms