explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EEBF

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=409,097.09..415,203.65 rows=97,705 width=239) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=409,097.09..409,341.35 rows=97,705 width=239) (actual rows= loops=)

  • Sort Key: block.schedule_event_id, block.start_dt, block.end_dt, (CASE WHEN block.created_by_app THEN 'OFFICE_HOURS'::character varying ELSE block.block_type END), block.title, (array_agg(DISTINCT appttypes.appointment_type_id)), block.type_of_meetings, tempex.frequency, tempex.start_hour, tempex.start_minute, tempex.end_hour, tempex.end_minute, tempex.te_type, tempex.day_index, tempex.day_in_week, tempex.weeks_in_month, tempex.repeats_never, tempex.weekly_days, tempex.time_zone, tempex.start_date, tempex.end_date, appt.meeting_type, freebusy.busy_type, (CASE WHEN (ea.external_appointment_id IS NOT NULL) THEN 'EXTERNAL'::text ELSE NULL::text END)
3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=384,034.55..392,339.48 rows=97,705 width=239) (actual rows= loops=)

  • Group Key: (CASE WHEN block.created_by_app THEN 'OFFICE_HOURS'::character varying ELSE block.block_type END), block.schedule_event_id, tempex.frequency, tempex.start_hour, tempex.start_minute, tempex.end_hour, tempex.end_minute, tempex.te_type, tempex.day_index, tempex.day_in_week, tempex.weeks_in_month, tempex.repeats_never, tempex.weekly_days, tempex.time_zone, tempex.start_date, tempex.end_date, appt.meeting_type, freebusy.busy_type, (CASE WHEN (ea.external_appointment_id IS NOT NULL) THEN 'EXTERNAL'::text ELSE NULL::text END)
4. 0.000 0.000 ↓ 0.0

Sort (cost=384,034.55..384,278.82 rows=97,705 width=215) (actual rows= loops=)

  • Sort Key: (CASE WHEN block.created_by_app THEN 'OFFICE_HOURS'::character varying ELSE block.block_type END), block.schedule_event_id, tempex.frequency, tempex.start_hour, tempex.start_minute, tempex.end_hour, tempex.end_minute, tempex.te_type, tempex.day_index, tempex.day_in_week, tempex.weeks_in_month, tempex.repeats_never, tempex.weekly_days, tempex.time_zone, tempex.start_date, tempex.end_date, appt.meeting_type, freebusy.busy_type, (CASE WHEN (ea.external_appointment_id IS NOT NULL) THEN 'EXTERNAL'::text ELSE NULL::text END)
5. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=357,337.42..368,063.44 rows=97,705 width=215) (actual rows= loops=)

  • Merge Cond: (block.schedule_id = appt.schedule_id)
  • Join Filter: (appt.tenant_id = block.tenant_id)
6. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=82,429.49..89,283.76 rows=959 width=172) (actual rows= loops=)

  • Merge Cond: (block.schedule_id = freebusy.schedule_id)
  • Join Filter: (freebusy.tenant_id = block.tenant_id)
7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=65,354.71..72,161.16 rows=774 width=167) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=65,354.29..69,052.50 rows=774 width=159) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=65,353.86..65,357.77 rows=774 width=92) (actual rows= loops=)

  • Merge Cond: (schedule.schedule_id = ea.schedule_id)
  • Join Filter: (ea.tenant_id = block.tenant_id)
10. 0.000 0.000 ↓ 0.0

Sort (cost=65,328.68..65,330.61 rows=774 width=92) (actual rows= loops=)

  • Sort Key: schedule.schedule_id
11. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,718.69..65,291.54 rows=774 width=92) (actual rows= loops=)

  • Hash Cond: (block.schedule_id = schedule.schedule_id)
12. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on schedule_block block (cost=3,495.68..64,036.98 rows=2,222 width=84) (actual rows= loops=)

  • Recheck Cond: (tenant_id = 511)
  • Filter: ((retired_date IS NULL) AND (((start_dt <= '2019-11-08 00:00:00+00'::timestamp with time zone) AND (end_dt > now())) OR (updated_date >= '2019-11-06 00:00:00+00'::timestamp with time zone)))
13. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_fk_schedule_block_tenant_id (cost=0.00..3,495.12 rows=129,960 width=0) (actual rows= loops=)

  • Index Cond: (tenant_id = 511)
14. 0.000 0.000 ↓ 0.0

Hash (cost=960.54..960.54 rows=8,076 width=8) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on schedule (cost=157.88..960.54 rows=8,076 width=8) (actual rows= loops=)

  • Recheck Cond: (tenant_id = 511)
  • Filter: ((schedule_type)::text = 'C'::text)
16. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_schedule_tenant_id (cost=0.00..155.86 rows=8,076 width=0) (actual rows= loops=)

  • Index Cond: (tenant_id = 511)
17. 0.000 0.000 ↓ 0.0

Sort (cost=25.19..25.19 rows=1 width=24) (actual rows= loops=)

  • Sort Key: ea.schedule_id
18. 0.000 0.000 ↓ 0.0

Index Scan using idx_fk_external_appointment_tenant_id on external_appointment ea (cost=0.14..25.18 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (tenant_id = 511)
  • Filter: ((start_dt >= '2019-11-08 00:00:00+00'::timestamp with time zone) AND (start_dt <= '2019-11-12 00:00:00+00'::timestamp with time zone))
19. 0.000 0.000 ↓ 0.0

Index Scan using pk_temporal_expression on temporal_expression tempex (cost=0.42..4.74 rows=1 width=83) (actual rows= loops=)

  • Index Cond: (temporal_expression_id = block.temporal_expression_id)
20. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_schedule_block_appointment_types on schedule_block_appointment_types appttypes (cost=0.42..3.99 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (schedule_event_id = block.schedule_event_id)
21. 0.000 0.000 ↓ 0.0

Sort (cost=17,074.67..17,082.08 rows=2,964 width=21) (actual rows= loops=)

  • Sort Key: freebusy.schedule_id
22. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on free_busy freebusy (cost=1,254.69..16,903.75 rows=2,964 width=21) (actual rows= loops=)

  • Recheck Cond: ((start_dt >= '2019-11-08 00:00:00+00'::timestamp with time zone) AND (start_dt <= '2019-11-12 00:00:00+00'::timestamp with time zone))
  • Filter: (((busy_type)::text = 'BUSY'::text) AND (tenant_id = 511))
23. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_free_busy_start_dt (cost=0.00..1,253.95 rows=6,353 width=0) (actual rows= loops=)

  • Index Cond: ((start_dt >= '2019-11-08 00:00:00+00'::timestamp with time zone) AND (start_dt <= '2019-11-12 00:00:00+00'::timestamp with time zone))
24. 0.000 0.000 ↓ 0.0

Materialize (cost=274,906.57..275,319.61 rows=82,607 width=28) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Sort (cost=274,906.57..275,113.09 rows=82,607 width=28) (actual rows= loops=)

  • Sort Key: appt.schedule_id
26. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on appointment appt (cost=28,463.95..266,606.32 rows=82,607 width=28) (actual rows= loops=)

  • Recheck Cond: (tenant_id = 511)
  • Filter: ((start_dt < '2019-11-12 00:00:00+00'::timestamp with time zone) AND ((meeting_type)::text = 'RESERVED_TIME'::text))
27. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_fk_appointment_tenant_id (cost=0.00..28,443.30 rows=798,383 width=0) (actual rows= loops=)

  • Index Cond: (tenant_id = 511)