explain.depesz.com

PostgreSQL's explain analyze made readable

Result: usdY

Settings
# exclusive inclusive rows x rows loops node
1. 447.829 447.829 ↓ 13.4 2,674 1

CTE Scan on teachers (cost=210,169.00..210,173.00 rows=200 width=8) (actual time=443.057..447.829 rows=2,674 loops=1)

  • Output: teachers.pid
  • Buffers: shared hit=57215
2.          

CTE pid_vs_slots

3. 71.896 440.562 ↑ 29.6 4,818 1

HashAggregate (cost=192,274.95..193,699.47 rows=142,452 width=24) (actual time=439.223..440.562 rows=4,818 loops=1)

  • Output: i.pid, i.week_slot_start, count(*)
  • Group Key: i.pid, i.week_slot_start
  • Filter: (count(*) >= 40)
  • Buffers: shared hit=57215
4. 116.830 368.666 ↓ 1.2 192,720 1

Bitmap Heap Scan on public.t_index5 i (cost=10,787.06..190,727.74 rows=154,721 width=16) (actual time=261.556..368.666 rows=192,720 loops=1)

  • Output: i.id, i.duration, i.week_slot_start, i.slot_start, i.slots_containing, i.slots_overlapping_inverse, i.pid
  • Recheck Cond: ((i.duration = 3600) 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))
  • Filter: ((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))
  • Heap Blocks: exact=46831
  • Buffers: shared hit=57215
5. 251.836 251.836 ↓ 1.2 192,720 1

Bitmap Index Scan on t_index5_i6 (cost=0.00..10,748.38 rows=154,750 width=0) (actual time=251.836..251.836 rows=192,720 loops=1)

  • Index Cond: ((i.duration = 3600) 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=10384
6.          

CTE teachers

7. 3.416 447.331 ↓ 13.4 2,674 1

GroupAggregate (cost=15,043.01..16,469.53 rows=200 width=8) (actual time=443.055..447.331 rows=2,674 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=57215
8. 1.991 443.915 ↑ 29.6 4,818 1

Sort (cost=15,043.01..15,399.14 rows=142,452 width=16) (actual time=443.014..443.915 rows=4,818 loops=1)

  • Output: pid_vs_slots.pid, pid_vs_slots.week_slot_start
  • Sort Key: pid_vs_slots.pid
  • Sort Method: quicksort Memory: 418kB
  • Buffers: shared hit=57215
9. 441.924 441.924 ↑ 29.6 4,818 1

CTE Scan on pid_vs_slots (cost=0.00..2,849.04 rows=142,452 width=16) (actual time=439.227..441.924 rows=4,818 loops=1)

  • Output: pid_vs_slots.pid, pid_vs_slots.week_slot_start
  • Buffers: shared hit=57215
Planning time : 0.230 ms
Execution time : 448.487 ms