explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8gQ6 : Optimization for: plan #lf0j

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 14.343 87.475 ↓ 0.0 0 1

Hash Join (cost=1,344.32..1,547.04 rows=1 width=20) (actual time=87.474..87.475 rows=0 loops=1)

  • Hash Cond: (schedule_appointment_resources.appointment_id = schedule_appointments.id)
  • Join Filter: ((schedule_appointments.starts_at < activities.ends_at) AND (schedule_appointments.ends_at > activities.starts_at))
2. 26.508 73.057 ↓ 8.7 72,107 1

Nested Loop (cost=857.76..1,029.50 rows=8,254 width=24) (actual time=1.944..73.057 rows=72,107 loops=1)

3. 0.063 2.229 ↓ 6.4 32 1

Nested Loop (cost=857.33..898.80 rows=5 width=29) (actual time=1.919..2.229 rows=32 loops=1)

4. 0.052 1.942 ↓ 6.4 32 1

HashAggregate (cost=856.90..856.95 rows=5 width=4) (actual time=1.913..1.942 rows=32 loops=1)

  • Group Key: schedule_resource_activities.id
5. 0.028 1.890 ↓ 6.4 32 1

Nested Loop (cost=30.18..856.88 rows=5 width=4) (actual time=0.194..1.890 rows=32 loops=1)

6. 0.209 1.798 ↓ 4.0 32 1

Hash Join (cost=29.90..851.37 rows=8 width=8) (actual time=0.177..1.798 rows=32 loops=1)

  • Hash Cond: (schedule_resource_activities.resource_id = location_resources.resource_id)
7. 1.523 1.523 ↓ 2.1 896 1

Index Scan using schedule_resource_activities_start_idx_2018 on schedule_resource_activities (cost=0.42..817.12 rows=417 width=17) (actual time=0.019..1.523 rows=896 loops=1)

  • Index Cond: ((starts_at >= '2019-11-14 23:00:00'::timestamp without time zone) AND (starts_at < '2019-11-15 23:00:00'::timestamp without time zone))
  • Filter: ((end_version IS NULL) AND ((NOT pending) OR (start_version IS NULL)) AND ((resource_type)::text = 'User'::text))
  • Rows Removed by Filter: 321
8. 0.014 0.066 ↓ 1.1 33 1

Hash (cost=29.10..29.10 rows=30 width=9) (actual time=0.066..0.066 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
9. 0.038 0.052 ↓ 1.1 33 1

Bitmap Heap Scan on location_resources (cost=4.54..29.10 rows=30 width=9) (actual time=0.021..0.052 rows=33 loops=1)

  • Recheck Cond: (location_id = 26)
  • Filter: ((resource_type)::text = 'User'::text)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=16
10. 0.014 0.014 ↓ 1.0 35 1

Bitmap Index Scan on index_location_resources_on_location_id (cost=0.00..4.54 rows=34 width=0) (actual time=0.014..0.014 rows=35 loops=1)

  • Index Cond: (location_id = 26)
11. 0.064 0.064 ↑ 1.0 1 32

Index Only Scan using schedules_timebook_idx on schedules (cost=0.28..0.68 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=32)

  • Index Cond: (id = schedule_resource_activities.schedule_id)
  • Heap Fetches: 32
12. 0.224 0.224 ↑ 1.0 1 32

Index Scan using schedule_resource_activities_pkey on schedule_resource_activities activities (cost=0.43..8.36 rows=1 width=29) (actual time=0.007..0.007 rows=1 loops=32)

  • Index Cond: (id = schedule_resource_activities.id)
13. 44.320 44.320 ↓ 2.6 2,253 32

Index Only Scan using schedule_appointment_resources_timebook_idx on schedule_appointment_resources (cost=0.43..17.58 rows=856 width=13) (actual time=0.011..1.385 rows=2,253 loops=32)

  • Index Cond: ((resource_type = (activities.resource_type)::text) AND (resource_id = activities.resource_id))
  • Heap Fetches: 25661
14. 0.007 0.075 ↑ 17.7 13 1

Hash (cost=483.69..483.69 rows=230 width=20) (actual time=0.075..0.075 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.068 0.068 ↑ 17.7 13 1

Index Scan using appointment_yearly_idx_2019 on schedule_appointments (cost=0.42..483.69 rows=230 width=20) (actual time=0.024..0.068 rows=13 loops=1)

  • Index Cond: ((ends_at >= '2019-11-14 23:00:00'::timestamp without time zone) AND (ends_at < '2019-11-15 23:00:00'::timestamp without time zone))
Planning time : 2.805 ms
Execution time : 87.593 ms