explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9cHq

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 83.576 ↑ 1.0 5 1

Limit (cost=13,305.73..13,305.75 rows=5 width=236) (actual time=83.576..83.576 rows=5 loops=1)

  • Output: teacher_filter.student, teacher_filter.teacher, teacher_filter.instrument, teacher_filter.distance, teacher_filter.attractiveness, teacher_filter.huff_raw, ((('100'::double precision * teacher_filter.huff_raw) / sum(teacher_filter.huff_raw) OVER (?))), teacher_filter.genre, teacher_filter.age, teacher_filter.level, teacher_filter.reliability, teacher_filter.quality, teacher_filter.availability
  • Buffers: shared hit=1361 read=6315, temp read=331 written=332
2.          

CTE teacher_filter

3. 6.689 83.192 ↑ 11.8 212 1

Result (cost=10,540.56..13,170.46 rows=2,500 width=212) (actual time=74.940..83.192 rows=212 loops=1)

  • Output: st.id, st.teacher_id, te.instrument, (_st_distance(geography(st.address_box), '0101000020E61000009A99999999592140560E2DB29D0F4940'::geography, '0'::double precision, false) / '1000'::double precision), thesis_attractiveness_combined(te.reliability, te.quality, te.availability, 'G_12'::text, 'Child'::age_class, 'Beginner'::proficiency, te.genre, te.age, te.level), (((thesis_attractiveness_combined(te.reliability, te.quality, te.availability, 'G_12'::text, 'Child'::age_class, 'Beginner'::proficiency, te.genre, te.age, te.level) + '0.0001'::double precision) ^ '1'::double precision) / GREATEST('0.0001'::double precision, ((_st_distance(geography(st.address_box), '0101000020E61000009A99999999592140560E2DB29D0F4940'::geography, '0'::double precision, false) / '1000'::double precision) ^ '2'::double precision))), te.genre, te.age, te.level, te.reliability, te.quality, te.availability, ((st.address_transform <-> '0101000020110F000006B24E2A81782D41786B7FBDD9AB5841'::geometry))
  • Buffers: shared hit=1361 read=6315, temp read=331 written=332
4. 0.677 76.503 ↑ 11.8 212 1

Unique (cost=10,540.56..10,582.96 rows=2,500 width=308) (actual time=74.718..76.503 rows=212 loops=1)

  • Output: st.id, st.teacher_id, te.instrument, te.genre, te.age, te.level, te.reliability, te.quality, te.availability, ((st.address_transform <-> '0101000020110F000006B24E2A81782D41786B7FBDD9AB5841'::geometry)), st.address_box
  • Buffers: shared hit=1355 read=6315, temp read=331 written=332
5. 15.994 75.826 ↑ 1.0 8,405 1

Sort (cost=10,540.56..10,561.76 rows=8,480 width=308) (actual time=74.717..75.826 rows=8,405 loops=1)

  • Output: st.id, st.teacher_id, te.instrument, te.genre, te.age, te.level, te.reliability, te.quality, te.availability, ((st.address_transform <-> '0101000020110F000006B24E2A81782D41786B7FBDD9AB5841'::geometry)), st.address_box
  • Sort Key: st.teacher_id, ((st.address_transform <-> '0101000020110F000006B24E2A81782D41786B7FBDD9AB5841'::geometry))
  • Sort Method: external merge Disk: 2648kB
  • Buffers: shared hit=1355 read=6315, temp read=331 written=332
6. 20.858 59.832 ↑ 1.0 8,405 1

Hash Join (cost=216.90..9,987.25 rows=8,480 width=308) (actual time=20.608..59.832 rows=8,405 loops=1)

  • Output: st.id, st.teacher_id, te.instrument, te.genre, te.age, te.level, te.reliability, te.quality, te.availability, (st.address_transform <-> '0101000020110F000006B24E2A81782D41786B7FBDD9AB5841'::geometry), st.address_box
  • Inner Unique: true
  • Hash Cond: (st.teacher_id = te.id)
  • Buffers: shared hit=1355 read=6315
7. 38.115 38.115 ↑ 1.0 100,000 1

Seq Scan on public.student_hesse_100_rtree st (cost=0.00..8,487.00 rows=100,000 width=267) (actual time=19.721..38.115 rows=100,000 loops=1)

  • Output: st.id, st.teacher_id, st.branch, st.latitude_val, st.longitude_val, st.address_box, st.address_mid, st.address_transform
  • Buffers: shared hit=1172 read=6315
8. 0.043 0.859 ↑ 1.0 212 1

Hash (cost=214.25..214.25 rows=212 width=161) (actual time=0.859..0.859 rows=212 loops=1)

  • Output: te.instrument, te.reliability, te.quality, te.availability, te.genre, te.age, te.level, te.id
  • Buckets: 1024 Batches: 1 Memory Usage: 50kB
  • Buffers: shared hit=183
9. 0.816 0.816 ↑ 1.0 212 1

Seq Scan on public.teacher_synth_large te (cost=0.00..214.25 rows=212 width=161) (actual time=0.047..0.816 rows=212 loops=1)

  • Output: te.instrument, te.reliability, te.quality, te.availability, te.genre, te.age, te.level, te.id
  • Filter: (te.instrument @> '{I_19}'::text[])
  • Rows Removed by Filter: 2288
  • Buffers: shared hit=183
10. 0.047 83.576 ↑ 500.0 5 1

Sort (cost=135.27..141.52 rows=2,500 width=236) (actual time=83.576..83.576 rows=5 loops=1)

  • Output: teacher_filter.student, teacher_filter.teacher, teacher_filter.instrument, teacher_filter.distance, teacher_filter.attractiveness, teacher_filter.huff_raw, ((('100'::double precision * teacher_filter.huff_raw) / sum(teacher_filter.huff_raw) OVER (?))), teacher_filter.genre, teacher_filter.age, teacher_filter.level, teacher_filter.reliability, teacher_filter.quality, teacher_filter.availability
  • Sort Key: teacher_filter.huff_raw DESC
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=1361 read=6315, temp read=331 written=332
11. 0.150 83.529 ↑ 11.8 212 1

WindowAgg (cost=0.00..93.75 rows=2,500 width=236) (actual time=83.481..83.529 rows=212 loops=1)

  • Output: teacher_filter.student, teacher_filter.teacher, teacher_filter.instrument, teacher_filter.distance, teacher_filter.attractiveness, teacher_filter.huff_raw, (('100'::double precision * teacher_filter.huff_raw) / sum(teacher_filter.huff_raw) OVER (?)), teacher_filter.genre, teacher_filter.age, teacher_filter.level, teacher_filter.reliability, teacher_filter.quality, teacher_filter.availability
  • Buffers: shared hit=1361 read=6315, temp read=331 written=332
12. 83.379 83.379 ↑ 11.8 212 1

CTE Scan on teacher_filter (cost=0.00..50.00 rows=2,500 width=228) (actual time=74.956..83.379 rows=212 loops=1)

  • Output: teacher_filter.student, teacher_filter.teacher, teacher_filter.instrument, teacher_filter.distance, teacher_filter.attractiveness, teacher_filter.huff_raw, teacher_filter.genre, teacher_filter.age, teacher_filter.level, teacher_filter.reliability, teacher_filter.quality, teacher_filter.availability
  • Buffers: shared hit=1361 read=6315, temp read=331 written=332