explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fs13

Settings
# exclusive inclusive rows x rows loops node
1. 89,118.200 89,118.200 ↓ 66.8 13,370 1

CTE Scan on teachers (cost=1,212,992.88..1,212,996.88 rows=200 width=8) (actual time=89,097.914..89,118.200 rows=13,370 loops=1)

  • Output: teachers.pid
  • Buffers: shared hit=891337, temp read=3062 written=3062
2.          

CTE pid_vs_slots

3. 104.545 89,089.551 ↑ 28.9 24,090 1

GroupAggregate (cost=1,108,089.22..1,124,493.18 rows=696,561 width=24) (actual time=88,895.598..89,089.551 rows=24,090 loops=1)

  • Output: i.pid, i.week_slot_start, count(*)
  • Group Key: i.pid, i.week_slot_start
  • Filter: (count(*) >= 40)
  • Buffers: shared hit=891337, temp read=3062 written=3062
4. 532.131 88,985.006 ↓ 1.3 963,600 1

Sort (cost=1,108,089.22..1,109,976.89 rows=755,068 width=16) (actual time=88,895.552..88,985.006 rows=963,600 loops=1)

  • Output: i.pid, i.week_slot_start
  • Sort Key: i.pid, i.week_slot_start
  • Sort Method: external merge Disk: 24488kB
  • Buffers: shared hit=891337, temp read=3062 written=3062
5. 988.795 88,452.875 ↓ 1.3 963,600 1

Bitmap Heap Scan on public.t_index4_big i (cost=143,593.45..1,034,371.00 rows=755,068 width=16) (actual time=87,525.234..88,452.875 rows=963,600 loops=1)

  • Output: i.pid, i.week_slot_start
  • Recheck Cond: ((i.duration = 3600) AND (i.slot_start >= '2020-05-18 00:00:00'::timestamp without time zone) AND (i.slot_start < '2021-02-22 00:00:00'::timestamp without time zone) AND (i.week_slot_start = ANY ('{"2001-01-01 18:00:00","2001-01-01 19:00:00","2001-01-01 20:00:00","2001-01-02 11:00:00","2001-01-02 12:00:00","2001-01-02 13:00:00","2001-01-03 12:00:00","2001-01-03 13:00:00","2001-01-03 14:00:00","2001-01-04 19:00:00","2001-01-04 20:00:00","2001-01-04 21:00:00","2001-01-05 17:00:00","2001-01-05 18:00:00","2001-01-05 19:00:00","2001-01-06 12:00:00","2001-01-06 13:00:00","2001-01-06 14:00:00","2001-01-07 21:00:00","2001-01-07 22:00:00","2001-01-07 23:00:00"}'::timestamp without time zone[])) AND ('{3,6}'::integer[] <@ i.slots_overlapping_inverse))
  • Heap Blocks: exact=233864
  • Buffers: shared hit=891337
6. 87,464.080 87,464.080 ↓ 1.3 963,600 1

Bitmap Index Scan on t_index4_big_i7 (cost=0.00..143,404.68 rows=755,068 width=0) (actual time=87,464.080..87,464.080 rows=963,600 loops=1)

  • Index Cond: ((i.duration = 3600) AND (i.slot_start >= '2020-05-18 00:00:00'::timestamp without time zone) AND (i.slot_start < '2021-02-22 00:00:00'::timestamp without time zone) AND (i.week_slot_start = ANY ('{"2001-01-01 18:00:00","2001-01-01 19:00:00","2001-01-01 20:00:00","2001-01-02 11:00:00","2001-01-02 12:00:00","2001-01-02 13:00:00","2001-01-03 12:00:00","2001-01-03 13:00:00","2001-01-03 14:00:00","2001-01-04 19:00:00","2001-01-04 20:00:00","2001-01-04 21:00:00","2001-01-05 17:00:00","2001-01-05 18:00:00","2001-01-05 19:00:00","2001-01-06 12:00:00","2001-01-06 13:00:00","2001-01-06 14:00:00","2001-01-07 21:00:00","2001-01-07 22:00:00","2001-01-07 23:00:00"}'::timestamp without time zone[])) AND ('{3,6}'::integer[] <@ i.slots_overlapping_inverse))
  • Buffers: shared hit=657473
7.          

CTE teachers

8. 16.199 89,115.947 ↓ 66.8 13,370 1

GroupAggregate (cost=81,532.08..88,499.69 rows=200 width=8) (actual time=89,097.912..89,115.947 rows=13,370 loops=1)

  • Output: pid_vs_slots.pid
  • Group Key: pid_vs_slots.pid
  • Filter: (count(DISTINCT date_part('isodow'::text, pid_vs_slots.week_slot_start)) >= 1)
  • Buffers: shared hit=891337, temp read=3062 written=3062
9. 4.479 89,099.748 ↑ 28.9 24,090 1

Sort (cost=81,532.08..83,273.49 rows=696,561 width=16) (actual time=89,097.861..89,099.748 rows=24,090 loops=1)

  • Output: pid_vs_slots.pid, pid_vs_slots.week_slot_start
  • Sort Key: pid_vs_slots.pid
  • Sort Method: quicksort Memory: 1898kB
  • Buffers: shared hit=891337, temp read=3062 written=3062
10. 89,095.269 89,095.269 ↑ 28.9 24,090 1

CTE Scan on pid_vs_slots (cost=0.00..13,931.22 rows=696,561 width=16) (actual time=88,895.601..89,095.269 rows=24,090 loops=1)

  • Output: pid_vs_slots.pid, pid_vs_slots.week_slot_start
  • Buffers: shared hit=891337, temp read=3062 written=3062
Planning time : 0.273 ms
Execution time : 89,123.002 ms