explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VXlr : Birthday email all conditionals

Settings
# exclusive inclusive rows x rows loops node
1. 0.794 2,739.265 ↓ 60.2 1,264 1

Nested Loop (cost=0.43..254,373.34 rows=21 width=4) (actual time=9.914..2,739.265 rows=1,264 loops=1)

  • Buffers: shared hit=100735 read=5252
  • I/O Timings: read=1927.922
2. 768.971 768.971 ↓ 19.6 3,250 1

Seq Scan on person p (cost=0.00..246,475.70 rows=166 width=4) (actual time=0.658..768.971 rows=3,250 loops=1)

  • Filter: ((date_part('month'::text, (birthday)::timestamp without time zone) = '1'::double precision) AND (date_part('day'::text, (birthday)::timestamp without time zone) = '28'::double precision))
  • Rows Removed by Filter: 6647482
  • Buffers: shared hit=80216
3. 12.569 1,969.500 ↓ 0.0 0 3,250

Index Scan using pk_dcteacher on dcteacher t (cost=0.43..47.57 rows=1 width=4) (actual time=0.606..0.606 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=20519 read=5252
  • I/O Timings: read=1927.922
4.          

SubPlan (for Index Scan)

5. 0.825 1,948.342 ↓ 0.0 0 1,067

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

  • Buffers: shared hit=5337 read=5252
  • I/O Timings: read=1927.922
6. 767.173 767.173 ↑ 21.0 1 1,067

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

  • Index Cond: (createduserid = t.teacherid)
  • Buffers: shared hit=2190 read=2035
  • I/O Timings: read=759.681
7. 1,180.344 1,180.344 ↓ 0.0 0 1,052

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

  • Index Cond: (proposalid = dcproposal.proposalid)
  • Filter: (workflowstatusid = 3)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=3147 read=3217
  • I/O Timings: read=1168.241
8. 0.409 8.589 ↑ 1.0 1 409

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

  • Buffers: shared hit=3449
9. 0.818 8.180 ↑ 1.0 1 409

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

  • Group Key: teacherdeactivationevent.teacherid, teacherdeactivationeventreason.reason
  • Buffers: shared hit=3449
10. 2.454 7.362 ↑ 1.0 1 409

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

  • Sort Key: teacherdeactivationeventreason.reason
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3449
11. 0.643 4.908 ↑ 1.0 1 409

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

  • Buffers: shared hit=3449
12. 2.045 2.045 ↑ 1.0 1 409

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

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

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

  • Index Cond: (teacherdeactivationeventid = teacherdeactivationevent.id)
  • Buffers: shared hit=1778
Planning time : 1.818 ms
Execution time : 2,739.535 ms