explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8JOj

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 115.269 ↑ 1.0 1 1

Aggregate (cost=86,111.48..86,111.49 rows=1 width=8) (actual time=115.268..115.269 rows=1 loops=1)

2. 2.628 115.258 ↑ 75.0 44 1

Nested Loop (cost=10,118.33..86,103.23 rows=3,301 width=0) (actual time=85.059..115.258 rows=44 loops=1)

3. 2.159 85.370 ↓ 1.2 5,452 1

HashAggregate (cost=10,118.05..10,162.06 rows=4,401 width=4) (actual time=84.376..85.370 rows=5,452 loops=1)

  • Group Key: u0.id
4. 78.758 83.211 ↓ 1.2 5,452 1

Hash Left Join (cost=63.23..10,107.05 rows=4,401 width=4) (actual time=1.483..83.211 rows=5,452 loops=1)

  • Hash Cond: (u2.entity_id = u3.id)
  • Filter: ((u4.user_id = 124) OR (u0.allocation_id IS NULL))
  • Rows Removed by Filter: 928188
5. 1.020 3.813 ↑ 1.0 5,452 1

Hash Left Join (cost=44.43..196.27 rows=5,452 width=12) (actual time=0.807..3.813 rows=5,452 loops=1)

  • Hash Cond: (u1.contract_id = u2.id)
6. 1.485 2.670 ↑ 1.0 5,452 1

Hash Left Join (cost=29.23..166.13 rows=5,452 width=12) (actual time=0.661..2.670 rows=5,452 loops=1)

  • Hash Cond: (u0.allocation_id = u1.id)
7. 0.565 0.565 ↑ 1.0 5,452 1

Seq Scan on asset_reassignmentchange u0 (cost=0.00..122.52 rows=5,452 width=8) (actual time=0.013..0.565 rows=5,452 loops=1)

8. 0.288 0.620 ↑ 1.0 810 1

Hash (cost=19.10..19.10 rows=810 width=8) (actual time=0.620..0.620 rows=810 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
9. 0.332 0.332 ↑ 1.0 810 1

Seq Scan on activity_allocation u1 (cost=0.00..19.10 rows=810 width=8) (actual time=0.011..0.332 rows=810 loops=1)

10. 0.039 0.123 ↑ 1.0 98 1

Hash (cost=13.98..13.98 rows=98 width=8) (actual time=0.123..0.123 rows=98 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
11. 0.084 0.084 ↑ 1.0 98 1

Seq Scan on customer_contract u2 (cost=0.00..13.98 rows=98 width=8) (actual time=0.007..0.084 rows=98 loops=1)

12. 0.198 0.640 ↑ 1.0 527 1

Hash (cost=12.21..12.21 rows=527 width=8) (actual time=0.640..0.640 rows=527 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
13. 0.319 0.442 ↑ 1.0 527 1

Hash Right Join (cost=1.09..12.21 rows=527 width=8) (actual time=0.047..0.442 rows=527 loops=1)

  • Hash Cond: (u4.entity_id = u3.id)
14. 0.111 0.111 ↑ 1.0 527 1

Seq Scan on customer_entity_users u4 (cost=0.00..8.27 rows=527 width=8) (actual time=0.012..0.111 rows=527 loops=1)

15. 0.006 0.012 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=4) (actual time=0.012..0.012 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.006 0.006 ↑ 1.0 4 1

Seq Scan on customer_entity u3 (cost=0.00..1.04 rows=4 width=4) (actual time=0.005..0.006 rows=4 loops=1)

17. 10.905 27.260 ↓ 0.0 0 5,452

Index Only Scan using asset_reassignmentchange_pkey on asset_reassignmentchange (cost=0.28..17.25 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=5,452)

  • Index Cond: (id = u0.id)
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (NOT (SubPlan 3)))
  • Rows Removed by Filter: 1
  • Heap Fetches: 5452
18.          

SubPlan (forIndex Only Scan)

19. 10.904 10.904 ↓ 0.0 0 5,452

Index Scan using activity_activity_reassignment_change_id_8b1671c7 on activity_activity u0_1 (cost=0.42..8.45 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=5,452)

  • Index Cond: (reassignment_change_id = asset_reassignmentchange.id)
  • Filter: ((NOT deleted) AND (ordered_arrival_datetime_from >= '2019-09-20 13:58:13.421403+00'::timestamp with time zone) AND (status <> 'finished'::text))
  • Rows Removed by Filter: 1
20. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on activity_activity u0_2 (cost=7,428.33..27,003.99 rows=1,563 width=4) (never executed)

  • Recheck Cond: ((ordered_arrival_datetime_from >= '2019-09-20 13:58:13.421403+00'::timestamp with time zone) AND (NOT deleted))
  • Filter: (status <> 'finished'::text)
21. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on activity_ac_allocat_3dee35_partial (cost=0.00..7,427.93 rows=11,035 width=0) (never executed)

  • Index Cond: (ordered_arrival_datetime_from >= '2019-09-20 13:58:13.421403+00'::timestamp with time zone)
22. 5.451 5.451 ↑ 1.0 1 5,451

Index Scan using activity_activity_reassignment_change_id_8b1671c7 on activity_activity u0_3 (cost=0.42..8.44 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=5,451)

  • Index Cond: (reassignment_change_id = asset_reassignmentchange.id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
Planning time : 2.938 ms
Execution time : 115.809 ms