explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lf0j

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 125.212 1,365.525 ↓ 0.0 0 1

Nested Loop (cost=858.19..5,891.31 rows=1 width=20) (actual time=1,365.525..1,365.525 rows=0 loops=1)

  • Join Filter: ((schedule_appointments.starts_at < activities.ends_at) AND (schedule_appointments.ends_at > activities.starts_at))
2. 72.997 303.065 ↓ 8.7 72,096 1

Nested Loop (cost=857.76..1,029.50 rows=8,253 width=24) (actual time=6.712..303.065 rows=72,096 loops=1)

3. 0.106 15.284 ↓ 6.4 32 1

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

4. 0.080 6.666 ↓ 6.4 32 1

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

  • Group Key: schedule_resource_activities.id
5. 0.061 6.586 ↓ 6.4 32 1

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

6. 0.360 6.397 ↓ 4.0 32 1

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

  • Hash Cond: (schedule_resource_activities.resource_id = location_resources.resource_id)
7. 5.852 5.852 ↓ 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.094..5.852 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.022 0.185 ↓ 1.1 33 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
9. 0.126 0.163 ↓ 1.1 33 1

Bitmap Heap Scan on location_resources (cost=4.54..29.10 rows=30 width=9) (actual time=0.059..0.163 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.037 0.037 ↓ 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.037..0.037 rows=35 loops=1)

  • Index Cond: (location_id = 26)
11. 0.128 0.128 ↑ 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.004..0.004 rows=1 loops=32)

  • Index Cond: (id = schedule_resource_activities.schedule_id)
  • Heap Fetches: 32
12. 8.512 8.512 ↑ 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.265..0.266 rows=1 loops=32)

  • Index Cond: (id = schedule_resource_activities.id)
13. 214.784 214.784 ↓ 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.024..6.712 rows=2,253 loops=32)

  • Index Cond: ((resource_type = (activities.resource_type)::text) AND (resource_id = activities.resource_id))
  • Heap Fetches: 25650
14. 937.248 937.248 ↓ 0.0 0 72,096

Index Scan using schedule_appointments_pkey on schedule_appointments (cost=0.43..0.57 rows=1 width=20) (actual time=0.013..0.013 rows=0 loops=72,096)

  • Index Cond: (id = schedule_appointment_resources.appointment_id)
  • Filter: (((status)::text <> 'cancelled'::text) AND (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))
  • Rows Removed by Filter: 1
Planning time : 14.721 ms
Execution time : 1,369.932 ms