explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qu4R

Settings
# exclusive inclusive rows x rows loops node
1. 969.849 5,082.186 ↑ 15.2 531 1

GroupAggregate (cost=639,014.03..662,825.46 rows=8,080 width=42) (actual time=3,577.107..5,082.186 rows=531 loops=1)

  • Output: mp.cluster_id, mp.cluster_name, (count(DISTINCT mp.subcluster_id) + count(DISTINCT CASE WHEN (mp.subcluster_id IS NULL) THEN 0 ELSE NULL::integer END)), count(DISTINCT mp.location_id), count(DISTINCT mp.machine_id), count(DISTINCT CASE WHEN ((rs.name)::text = ANY ('{a,d,r}'::text[])) THEN rm.route_id ELSE NULL::uuid END)
  • Group Key: mp.cluster_id, mp.cluster_name
  • Buffers: shared hit=1079173, temp read=7508 written=7508
2. 1,384.904 4,112.337 ↓ 1.2 1,187,480 1

Sort (cost=639,014.03..641,509.86 rows=998,334 width=156) (actual time=3,520.943..4,112.337 rows=1,187,480 loops=1)

  • Output: mp.cluster_id, mp.cluster_name, mp.subcluster_id, mp.location_id, mp.machine_id, rs.name, rm.route_id
  • Sort Key: mp.cluster_id, mp.cluster_name
  • Sort Method: external merge Disk: 60008kB
  • Buffers: shared hit=1079173, temp read=7508 written=7508
3. 667.883 2,727.433 ↓ 1.2 1,187,480 1

Hash Join (cost=417,660.50..472,262.22 rows=998,334 width=156) (actual time=1,579.681..2,727.433 rows=1,187,480 loops=1)

  • Output: mp.cluster_id, mp.cluster_name, mp.subcluster_id, mp.location_id, mp.machine_id, rs.name, rm.route_id
  • Hash Cond: (dp.machine_prediction_id = mp.id)
  • Buffers: shared hit=1079173
4. 480.086 480.086 ↑ 1.4 625,938 1

Index Only Scan using unique_date_for_machine on public.day_prediction dp (cost=0.42..38,213.30 rows=861,031 width=16) (actual time=0.014..480.086 rows=625,938 loops=1)

  • Output: dp.machine_prediction_id, dp.date
  • Heap Fetches: 355901
  • Buffers: shared hit=299368
5. 106.398 1,579.464 ↓ 1.6 169,640 1

Hash (cost=416,339.95..416,339.95 rows=105,610 width=172) (actual time=1,579.464..1,579.464 rows=169,640 loops=1)

  • Output: mp.cluster_id, mp.cluster_name, mp.subcluster_id, mp.location_id, mp.machine_id, mp.id, rm.route_id, rs.name
  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 16912kB
  • Buffers: shared hit=779805
6. 117.385 1,473.066 ↓ 1.6 169,640 1

Hash Left Join (cost=43,091.49..416,339.95 rows=105,610 width=172) (actual time=112.305..1,473.066 rows=169,640 loops=1)

  • Output: mp.cluster_id, mp.cluster_name, mp.subcluster_id, mp.location_id, mp.machine_id, mp.id, rm.route_id, rs.name
  • Hash Cond: (r.route_status_id = rs.id)
  • Buffers: shared hit=779805
7. 120.171 1,355.665 ↓ 1.6 169,640 1

Nested Loop Left Join (cost=43,090.35..414,886.68 rows=105,610 width=70) (actual time=112.276..1,355.665 rows=169,640 loops=1)

  • Output: mp.cluster_id, mp.cluster_name, mp.subcluster_id, mp.location_id, mp.machine_id, mp.id, rm.route_id, r.route_status_id
  • Buffers: shared hit=779804
8. 366.294 726.574 ↓ 1.6 169,640 1

Hash Right Join (cost=43,089.93..154,675.81 rows=105,610 width=54) (actual time=112.256..726.574 rows=169,640 loops=1)

  • Output: mp.cluster_id, mp.cluster_name, mp.subcluster_id, mp.location_id, mp.machine_id, mp.id, rm.route_id
  • Hash Cond: (rm.machine_id = mp.machine_id)
  • Buffers: shared hit=125392
9. 248.375 248.375 ↑ 1.0 536,802 1

Seq Scan on public.route_machine rm (cost=0.00..108,508.66 rows=538,966 width=20) (actual time=0.005..248.375 rows=536,802 loops=1)

  • Output: rm.created_at, rm.id, rm.route_id, rm.machine_id, rm.external_machine_id, rm.machine_status, rm.served_at, rm.served_by_id, rm.description, rm.removed, rm.machine_details, rm.expiry_date_after_refill, rm.help_id, rm.removed_by_fullname, rm.removed_by_id, rm.packed_in_warehouse, rm.moved_reason, rm.origin_date
  • Buffers: shared hit=103119
10. 24.228 111.905 ↓ 1.7 29,575 1

Hash (cost=42,866.83..42,866.83 rows=17,848 width=38) (actual time=111.905..111.905 rows=29,575 loops=1)

  • Output: mp.cluster_id, mp.cluster_name, mp.subcluster_id, mp.location_id, mp.machine_id, mp.id
  • Buckets: 32768 Batches: 1 Memory Usage: 2215kB
  • Buffers: shared hit=22273
11. 78.962 87.677 ↓ 1.7 29,575 1

Bitmap Heap Scan on public.machine_prediction mp (cost=4,188.36..42,866.83 rows=17,848 width=38) (actual time=14.177..87.677 rows=29,575 loops=1)

  • Output: mp.cluster_id, mp.cluster_name, mp.subcluster_id, mp.location_id, mp.machine_id, mp.id
  • Recheck Cond: (mp.company_id = 'a113dd3f-006c-4fe9-bc8f-ef6feae28287'::uuid)
  • Filter: (NOT mp.deleted)
  • Heap Blocks: exact=21624
  • Buffers: shared hit=22273
12. 8.715 8.715 ↓ 1.7 29,575 1

Bitmap Index Scan on machine_prediction__company_id__deleted (cost=0.00..4,183.90 rows=17,848 width=0) (actual time=8.715..8.715 rows=29,575 loops=1)

  • Index Cond: ((mp.company_id = 'a113dd3f-006c-4fe9-bc8f-ef6feae28287'::uuid) AND (mp.deleted = false))
  • Buffers: shared hit=649
13. 508.920 508.920 ↑ 1.0 1 169,640

Index Scan using route_pkey on public.route r (cost=0.42..2.45 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=169,640)

  • Output: r.created_at, r.updated_at, r.created_by, r.updated_by, r.id, r.name, r.company_id, r.route_status_id, r.route_type_id, r.start_at, r.end_at, r.start_filler_time, r.end_filler_time, r.filler_id, r.predict_route, r.deleted, r.deleted_at, r.deleted_by, r.elastic_id, r.created_by_fullname, r.deleted_by_fullname, r.help_id, r.end_location, r.start_location, r.last_refresh_stock, r.route_status_change_at, r.estimated_route_time_minutes, r.end_filler_time_manual, r.updated_by_fullname, r.get_pickup, r.estimated_route_drive_time_minutes, r.estimated_route_service_time_minutes, r.filtered_categories
  • Index Cond: (rm.route_id = r.id)
  • Buffers: shared hit=654412
14. 0.009 0.016 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=134) (actual time=0.015..0.016 rows=6 loops=1)

  • Output: rs.name, rs.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
15. 0.007 0.007 ↑ 1.0 6 1

Seq Scan on public.route_status rs (cost=0.00..1.06 rows=6 width=134) (actual time=0.005..0.007 rows=6 loops=1)

  • Output: rs.name, rs.id
  • Buffers: shared hit=1
Planning time : 1.364 ms
Execution time : 5,101.626 ms