explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yo8H : teacher birthday email collapsed birthday

Settings
# exclusive inclusive rows x rows loops node
1. 1.545 24.807 ↓ 1.6 1,264 1

Nested Loop (cost=116.06..296,242.57 rows=775 width=4) (actual time=0.916..24.807 rows=1,264 loops=1)

  • Buffers: shared hit=28960
2. 3.336 3.762 ↑ 1.9 3,250 1

Bitmap Heap Scan on person p (cost=115.63..19,011.84 rows=6,091 width=4) (actual time=0.814..3.762 rows=3,250 loops=1)

  • Recheck Cond: ((make_date(2000, (date_part('month'::text, (birthday)::timestamp without time zone))::integer, (date_part('day'::text, (birthday)::timestamp without time zone))::integer) = '2000-01-28'::date) AND (birthday IS NOT NULL))
  • Heap Blocks: exact=3172
  • Buffers: shared hit=3184
3. 0.426 0.426 ↑ 1.9 3,250 1

Bitmap Index Scan on person_birthday_collapsed_year (cost=0.00..114.11 rows=6,091 width=0) (actual time=0.426..0.426 rows=3,250 loops=1)

  • Index Cond: (make_date(2000, (date_part('month'::text, (birthday)::timestamp without time zone))::integer, (date_part('day'::text, (birthday)::timestamp without time zone))::integer) = '2000-01-28'::date)
  • Buffers: shared hit=12
4. 7.283 19.500 ↓ 0.0 0 3,250

Index Scan using pk_dcteacher on dcteacher t (cost=0.43..45.50 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=3,250)

  • Index Cond: (teacherid = p.personid)
  • Filter: ((NOT excluded) OR ((SubPlan 1) AND (SubPlan 2)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=25776
5.          

SubPlan (for Index Scan)

6. 0.075 8.536 ↓ 0.0 0 1,067

Nested Loop (cost=0.99..298.77 rows=14 width=0) (actual time=0.008..0.008 rows=0 loops=1,067)

  • Buffers: shared hit=10589
7. 3.201 3.201 ↑ 21.0 1 1,067

Index Scan using dcproposal_createduserid on dcproposal (cost=0.43..88.23 rows=21 width=4) (actual time=0.003..0.003 rows=1 loops=1,067)

  • Index Cond: (createduserid = t.teacherid)
  • Buffers: shared hit=4225
8. 5.260 5.260 ↓ 0.0 0 1,052

Index Scan using proposalworkflowstatus_altindex2 on proposalworkflowstatus (cost=0.56..10.01 rows=2 width=4) (actual time=0.005..0.005 rows=0 loops=1,052)

  • Index Cond: (proposalid = dcproposal.proposalid)
  • Filter: (workflowstatusid = 3)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=6364
9. 0.409 3.681 ↑ 1.0 1 409

Subquery Scan on x (cost=16.91..16.94 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=409)

  • Buffers: shared hit=3454
10. 0.000 3.272 ↑ 1.0 1 409

GroupAggregate (cost=16.91..16.93 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=409)

  • Group Key: teacherdeactivationevent.teacherid, teacherdeactivationeventreason.reason
  • Buffers: shared hit=3454
11. 0.409 3.272 ↑ 1.0 1 409

Sort (cost=16.91..16.92 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=409)

  • Sort Key: teacherdeactivationeventreason.reason
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3454
12. 0.304 2.863 ↑ 1.0 1 409

Nested Loop (cost=0.85..16.90 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=409)

  • Buffers: shared hit=3449
13. 1.227 1.227 ↑ 1.0 1 409

Index Scan using teacherdeactivationevent_teacherid on teacherdeactivationevent (cost=0.43..8.45 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=409)

  • Index Cond: (teacherid = t.teacherid)
  • Buffers: shared hit=1671
14. 1.332 1.332 ↑ 1.0 1 444

Index Scan using teacherdeactivationeventreason_teacherdeactivationeventid on teacherdeactivationeventreason (cost=0.43..8.45 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=444)

  • Index Cond: (teacherdeactivationeventid = teacherdeactivationevent.id)
  • Buffers: shared hit=1778
Planning time : 2.240 ms
Execution time : 25.000 ms